When you drag a cell with January in it with the fill handle (the small cross at the bottom right corner of the cell), Excel creates a series with February and March etc. This is quick and useful. It also works for days of the week. Eg drag a cell with Monday in it to get Tuesday, etc. This is an example of a custom list.
The F4 function key in Excel has a dual personality.
When used normally it will repeat your last action. This is useful for formatting, deleting rows and other operations that require a few keystrokes. Pressing F4 can reduce the keystrokes. F4 can also repeat running a macro.
One of Excel’s most useful Ribbon / toolbar icons is the Format Painter.
To use it first select a cell or range that has the format you want to use. Then you click the Format Painter icon and click another cell to apply all the formats that the original cell had.
The shortcut is
Alt + Enter
To insert a line break within a cell hold down the Alt key (next to your space bar) and press Enter. This inserts a fixed line break within the cell.
The SUM function is the function of choice to add up values. But when it comes to subtotalling, SUM leaves a lot to be desired. There is a specific function for subtotalling called, funnily enough, SUBTOTAL.
The SUM function can perform 3D sums. The method is useful in certain circumstances and is both efficient and flexible. See the example file link at the bottom of this blog post.
Do you know everything about Excel?
I’ve been using it for over 20 years! I’ve learned a lot in those years, but I’m still learning new things and this blog aims to share my knowledge. Of course new versions are always adding new features, so it can be hard to keep up.
I’m a CPA and this blog will discuss Excel topics that are directly applicable to Accountants and Finance staff. The content will vary from simple to complex; from functions to formatting; from Excel 2003 to Excel 2010.