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!
So David Cameron has outlined his plans today to create a “Happiness Index” to measure how happy people in the UK are. The government (through the Office of National Statistics) will spend £2m compiling the index which will first appear in publication in 2012.
Best that the ONS takes some advice to improve the results:
- Make sure not to ask questions to the following people:
- Give the £2m out first to your interviewees, then ask them if they’re happy.
- Alternatively, happiness is a cigar called “H******”, so spend your £2m on these first, give them out and ask your questions.
Mind you, a cheaper way of doing a Happiness Index is to ask everyone if they’re happy and know it and watch them clap their hands.
Those were jokes by the way, in case some hadn’t realised (cf. the Twitter joke trial). Mind you, not sure if any of them increased your happiness after reading them.