Referring to a spill in another formula
You can reference a spilled range in a formula. That way, whenever the spilled range changes size, your referencing formula automatically includes all the data. Above we have a filter formula that is spilling down four rows. Above we have a Unique formula that is referencing the spilled range. Note the # in the reference. […]
Spilled range as chart source
Using a spilled range as a chart source will now automatically update the chart when the source changes. Another good reason to embrace these ‘new’ formula in Excel. You can read more about one of these formulas here Below is a bar chart with the data source next to it. The data source is the […]
Value preview in formula bar.
Using Value Preview, you can see the value of each part of a formula in Excel. Just highlight the reference you are interested in, and a little box pops up that show you the value(s) in that reference. Or you can click on the parameter in the formula structure. More information and some videos showing […]
Search the right click menu
You can now search in the right click menu to find the tool you are looking for. You now get a search box when you right click on a cell, or use the keyboard shortcut key Shift F10 (or some keyboards have a dedicated key for it , see picture below.) The Search enables you […]
Data Validation drop down lists
Data validation lists are better than ever. The list will now automatically reduce to match what you have typed into the cell. This wasn’t the case for so long in Excel, but has been standard in forms on the internet for ages. It was possible to create a drop down box that did it, but […]
Navigation Pane
You can now turn on a Navigation Pane. Find it under View Menu. Shortcut keys: Alt W, K. This brings up a list of all the sheets in your workbook. Each sheet has an expand arrow, and if you click that it brings up a list of tables, charts, pictures, and sections of that sheet. […]
Clipboard memory knows what you just copied, and the thing before that as well…
I am generally not a fan of copying and pasting as it is usually an inefficient thing to do. But, sometimes it is helpful and if you are going to do it at least do it well. Did you know that everything you copy is stored on the clipboard memory? This means that you can […]
Line up – Use Align to get your graphs layout just right.
When you have graphs (Charts) or pictures in Excel and you want to get them lined up you can use Align. It is available on the Page Layout Menu (Alt, P AA) and also the Format Shape Menu. (Alt, JA, AA) You need to select the elements first. Use the Shift (or Ctrl) key (hold […]
Custom Number Format the rescue.
You can do a lot with custom number formats. One thing that is tricky is when you are presenting information and you want to have the units correct is allowing for 0 days, 1 day, 2 days. The lack of “s” when the unit is 1 is tricky, but can be sorted with a custom […]
Displaying Numbers just the way you want.
Displaying numbers in a cell is an important part of making your data easily readable. The way a number is displayed in a cell can be different to the data that is stored in the background. You can change the way the data looks by formatting the cell. Use the Number section on the Home […]