Automatic Drop Down List

Excel has a drop down list that is automatic. Check the image below

Tip_PickList

Things to note:

  • Cell A1 is bold – that tells Excel its a column heading.
  • I have entered two names, Fred and Mary
  • In cell A4 when you use Alt + down arrow Excel automatically displays the list of the entries from above.
  • You can use your arrow keys to select from the list and press Enter.

This feature is similar to another feature of typing f in cell A4 and Fred will appear in the cell as it matches the first letter from a word above.

Don’t Make Me Think: A Common Sense Approach to Web Usability, 2nd Edition (New Riders Publishing)

A must-have book for computer interface designers – its a quick and funny read and it is well laid out – as you’d expect.

Its full colour with many before and after pics demonstrating the ideas. He also has a great usability book out as well.

It might be getting a little dated theses days, but I think the ideas stand the test of time.

Great PowerPivot Site

SQLBI – this website is a great resource for the PowerPivots in Excel 2010 and Excel 2013. Some of it is quite Advanced. See also the DAXPatterns site. These guys have published a few books and I own a couple of them.

Moving around in the Formula Bar

The only time I edit in the cell is when I run a training session because I can zoom in and make the formula larger on the screen.

In practice I only edit in the Formula Bar. When editing in the Formula Bar you can use the Home and End keys to move to either end of the Formula Bar.

FormBar_1

Excel 2007 VBA Programmer’s Reference (Programmer to Programmer) (Wrox Publishing)

A brilliant reference book for Excel VBA – its the manual that you need when programming.

I always keep it close at hand. Includes useful programming snippets as well as complete code solutions.

The secret of getting ahead is getting started. The secret of getting started is breaking your complex overwhelming tasks into small manageable tasks, and then starting on the first one.

Mark Twain (1835-1910)

Remove formatting

The keyboard shortcut to remove all formats from the selected range, pressed in sequence, not held down.

Alt H E F

Excel 2007 Advanced Report Development (Wiley Publishing)

Great book that will assist you in creating pivot table reports in Excel from external data sources.

It takes you through step by step for each different data source. includes MS Query and SQL. includes instructions for pivot table creation.

For intermediate to advanced users.

How wonderful it is that nobody need wait a single moment before starting to improve the world.

Anne Frank (1929–1945)

A road well begun is the battle half won. The important thing is to make a beginning and get under way.
Soren Kierkegaard (1813-1855)

Rename current sheet

The keyboard shortcut to change the current sheet name, pressed in sequence, not held down.

Alt H O R