Wednesday, 25 May 2011

Excel Ideas

I've just attended a course on Microsoft Excel for PC, 2003 version. Lots of useful ideas, but how many will I be able to implement without a lot of thought on Excel for Mac 2011?
1. Create your own toolbar
Handy if you have put together a set of macros that you want to use together.
View>Toolbar>Customise toolbars>New
2. Macros
I'm fairly sure this wasn't in the last version of Excel for Mac, but it's back now.
Tools>Macro>Record New Macro
Macros can be recorded and used with just one workbook, or set up globally. When recording them, it's useful to bear in mind the difference between absolute and relative cell references. It's the $ sign that makes a cell reference absolute - eg $G$4. ON the PC you can make a cell reference absolute by clicking F4. On my Mac that brings up the dashboard and, irritatingly, starts one of my apps making an interminable gurgling noise.
3. Macro Icons
Yes, you can assign your macros to various key combos, but who remembers those a few days later?
View>Toolbar>Customise toolbar>Command>Macros>Custom button
The default is a smiley face, but you can right-clidck to change the picture on a PC. I haven't yet worked out which combination of keys does this on a Mac. If you don't want the icon on your toolbar any more, you can drag it away and drop it for ever if you hold down ALT at the same time.
4. Ideas for macros
One that's fairly straightforward and could be saved for global use. Format page as Landscape, fit to page, add standard header and footer. That's all under File>Page Setup
5. Subtotals
Sort data on the relevant column, then go to Data>Subtotals. Not only do you get handy subtotals for each heading, but you get a set of numbers, plus and minus signs on the left, that allow you to focus in on certain categories.
6. Protecting sheets
I do have a tendency to mess up my data analysis by changing sections of my tables that need to stay the same. This one is rather counter-intuitve. Highlight the cells you want to remain open. Format>Cells>Protection>Unlock. Then head to Tools>Protection>Protect Sheet and limit users to selecting unlocked cells. No need to set a password. To make it all editable once again, just reverse what you've done.
7. Wrapping text
Format>Cells>Alignment>Wrap text. Who would have guessed that command would be hiding under the Alignment heading? You're supposed to be able to put soft returns into cell text as well, but I can't make that work on my Mac.
8. Splitting words into different columns
I haven't tried this out yet, but it involves using the SEARCH function to look for spaces, recording in a new column how many characters before (or after) the space and then using that information with a LEFT or RIGHT function to pick up the second word and put it in another column.
9. Show all the functions in your Excel spreadsheet
On a PC you do this by pressing CTRL together with a key with three little symbols on it at the top left of the PC keyboard. I haven't yet spotted how I could do this on a Mac.
10. Counting
I would have expected COUNT to count all cells, but it only counts all data items. COUNTBLANK counts the empty data cells and it's COUNTA that counts all the cells.

No comments:

Post a Comment