I have a Bluetooth mouse (an Apple Magic Mouse). Sometimes it gets unresponsive when scrolling, where there is a lag before the scrolling starts.
The fix is simple. On a Mac, go to System Preferences > Bluetooth and delete the mouse and re-add it. If you’re on Windows. go through the control panel and do the same.
Why does this work? Well, it appears that interference with other devices causes the lag, so the delete and re-add resets the connection.
And the reason why is down to interference. It’s pretty obvious, but working in modern offices mean there are plenty of sources of electromagnetic (EM) interference which might prevent devices communicating with each other as they should.
Your laptop, tablet, smartphone, wireless keyboard and mouse, and any other device that connects wirelessly all use a section of the EM spectrum to communicate. As the number of devices increase, the more likely that part of the spectrum is going to be congested, much like cars on a motorway.
Some of you may be aware that this is often an issue for Wifi. The wifi spectrum is divided into different channels, and older routers used to all be programmed to pick out a specific channel (often channel ‘1’ or ’11’). If everyone were to use the same channel, this would be inefficient as these would be clogged up and other channels would be clear.
Newer routers, however, seek out the best channel to use to give you the fastest speed.
In the same way, Bluetooth devices can be affected. Although the Bluetooth specification means that devices should switch channels automatically if there is a problem, often it doesn’t do a good enough job.
To demonstrate this point, I placed an active Mifi device next to my computer. Following this, my mouse was having all sorts of problems. Deleting and re-adding my mouse solved the interference issues.
If you would like to scroll to a sheet in your (large) Excel model but didn’t want to keep holding down the scroll arrow for many seconds, you can simply right click the sheet name scroll arrows (see below).
If you’re using Office 2013, a dialogue box will pop up with a list of sheets (see pic attached), if an older or a Mac version of Office, a pop-up menu comes up with the first sheets and an option to select “More sheets…” which returns the same dialogue box.
Double click the sheet you want and you’ll be teleported straight to the sheet you need.
One of the most useful analytical tools in Excel is Pivot Tables. They allow you to create summary reports of data sets quickly and generally without fuss. At least usually it’s quicker than using a database program such as Microsoft Access to pull summaries together.
I won’t go into how to build a pivot table – there are plenty of online resources to do that, but I must highlight their limitations.
Firstly, pivot tables are not dynamic. That is to say that if the source data changes, the pivot table does not automatically update. You have to manually update each and every pivot table individually to refresh (right-click on the table and Refresh Data). Equally if you’re on manual calculations mode, F9 (Cmd+= for Macs) won’t update your pivot tables.
The best way to get round this is to build your table using SUMIF() or SUMIFS() (or equivalents if you want to count, find maximums, etc). These will be dynamic, so if you do update the source data, the changes will feed through.
The second problem with pivot tables is that their size easily change. For example if an extra item was added in the source data, the pivot table might add an extra row. But if you had linked the old output, the added line won’t automatically change the link and you might be linking to the wrong cell. So be careful!
But having said that, as long as you’re using Pivot Tables for output and not linking elsewhere you should be fine. Happy pivoting!
Time to revive my blog a bit. Yes, it’s been over two years and much has kept me busy. But given the feedback I’ve had from many at work on my Excel knowledge, I’ve decided to share some with the wider World Wide Web, to further the knowledge of
mankind spreadsheet users everywhere.
This tip revolves around passwords in Excel.
Some of you may know, there is a way of protecting sheets and workbooks by going to Tools > Protection > Protect Sheet… (or Protect Workbook…). This will often prevent people from editing sheets and unhiding hidden sheets. Many will assume this is a secure way to do this. But guess what, it’s not really.
Excel’s password protection is very weak. It is possible to break the password very easily by simply by downloading some freely available software online*, or by copying and pasting VBA code and running that code. This YouTube video is one example of the latter and shows how to crack an Excel password using a macro in less than 10 seconds.
Therefore, best practice is not to assume that password protection of an Excel sheet is secure and not to simply “hide” sheets that you don’t want people to see (delete sheets if not necessary). If necessary, it is better encrypt the spreadsheet by using WinZip to zip the file and adding password protection to that.
However, Excel’s password protection can still be useful, e.g. to stop people from making (accidental) changes to your spreadsheet.
Hope that helps!
* not recommended as you might be downloading malware. Think about it, if you’re downloading something that’s doing something dodgy, might it not be a dodgy piece of software?
Today is the 100th anniversary of Louis Prima, a legendary singer and musician from the swing era.
He is one of my favourite artists, and first performed in the 1920s. But his career only really took off in the 1940s, and in 1949, his partnership with singer Keely Smith, later to become his second wife, gave him a popularity that would eventually take him to Vegas.
For most people, he’ll probably be remembered mostly for “Sing, Sing, Sing”, which he wrote, but its popularity would be mostly credited to Benny Goodman’s classic version of the track. However, he did have his own legacy, providing the voice for King Louis in Disney’s Jungle Book and singing “I Wanna Be Like You”, one of the more memorable tracks in the film. Some videos for you to enjoy are below.
For now, I’ll save some more of my favourites from Louis for another time.
In the meantime, a very happy 100th birthday to Louis Prima, and hope he’s laughing and smiling looking down on us in his unique style!