Excel tips, trickery, and shortcuts
These are (mostly brief and mostly video) explanations of handy Excel short-cuts, time-savers, and how-to's that I have found useful over the years. I will be adding new items to this list roughly every week or thereabouts.
Locating specific words in a worksheet or table
Track down all cells that contain a specific word or phrase with conditional formatting. Much easier than using Find. Also filter out a list of products, for example, that contain the word "blue" somewhere in the description. Video
Manipulating text in Excel - 2 basic funtions
If you've used Excel mostly to manipulate numerical data, but have never used it to manipulate text, watch this video. It demonstrates 2 basic, but useful text functions: Left and Right.
Using PivotTables (Excel 2007/2010) to retrieve tabular cube data
This video is for people who access Microsoft Analysis Service cubes (sometimes called SSAS cubes) using a PivotTable. It specifically describes how to retrieve detailed data in tabular form that might then be saved as a CSV file, say, perhaps for import to another system.
SSAS cubes often have dimensions with user-defined (multilevel) hierarchies, such as time (years/quarters/months/days), territory (region/country/province), and so on. Negotiating these hierachies to get the level you want, and no other levels, can be tricky. Also, large table extracts can be slow to manipulate as you select the dimensions and members you want. So it helps to do the right steps in the right order to minimize the time taken.
Quick copying (autofill)
Copying a cell down a column needn't be a drag. See why ...
How to copy just the subtotals without the detail.
If you have created subtotals using Excel's Data-Subtotals function and then tried to copy just the subtotals to another place you know that Excel copies the detail rows too. If that's not what you want here is how to fix it. Video
Quick chart to visualize data
Create a chart with one key press. Video
Advanced Filter: Blanks and Non-Blanks
How to show just blanks or non-blanks with Advanced Filter. Video