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.

Monday, 10 January 2011

Learning Analytics and Knowledge course

Jumped right into this course with a look at Hunch.

When I arrived I realised I'd visited a couple of times before - it's a link I follow from blogs and Twitter on occasion. Answer a series of picture questions and Hunch learns enough about you to make some recommendations. Keep engaging with the questions and recommendations and you should, in theory, have some useful recommendations that could help you choose what to read, where to go, what to buy. In theory. As I say, I've been there before on occasion and I have neither remembered the name nor bookmarked it. But why not?

First of all, I didn't like the options. Multiple-choice always winds me up. I hate questioners who won't give me the freedom to choose my own words and interpretations. Often all their answers are wrong, either unequivocally wrong, or wrong from my perspective.

Q. Do you live in the suburbs, a major city or a rural area? I live in a good-sized town. Skip the question.
Q. Do you tend to support liberal or conservative politicians? I live in the UK. Ask me a sensible question. Skip.
Q. Which of these sorts of fries do you prefer? Fish and chips. Oh, but the options are American fast food. Skip.
Q. Is Barack Obama a Muslim? (!) Should the site be posing as a complete idiot? Skip and End.

So the style of it all wound me up. Assuming I live in the USA wound me up. And the summary of people's responses - which is supplied each time you answer - made me feel an outsider. Only thirty-nine percent of users had identified as female (or as aliens wearing dresses, it wasn't clear), 15% had identified as Europeans. Only 17% read more than three books a month. (And did 3% really say they had PhDs? Either Hunch attracts a crowd of intellectuals, or a lot of liars.) I didn't  feel at home.

And the recommendations. Well, it spotted that I like Apple products - but after I'd answered the 'Mac or PC' question that wasn't too tricky, was it? And I said I was European, but it  recommended me five New York city museums, and five US credit cards. I was recommended a dreary series of magazines, a series of books that you'd read only if really bored, and some blogs where I didn't get beyond the first post.

Did I bookmark it? No.
Is this what I would want from a set of learning analytics? Absolutely not.