With a formatted Excel table, you can turn the Total Row on or off easily, and it shows at the bottom of the table. Someone asked me how to add data to Excel Table with Total Row showing – they were hiding the totals every time they wanted to add data. You don't need to do that!
With Excel's conditional formatting options, you can highlight cells based on their values, or add colour scales, icons and data bars to the cells. There are built-in rules to highlight top or bottom values, and you can also create your own rules, as shown below.
Instead of adding a static title to your Pivot Chart, use a worksheet formula to create a dynamic chart title. Then, when you make a selection in the Report Filter, the chart title will change too. The video below shows the step-by-step instructions.
Did you make any New Year resolutions that involve eating better, or getting in shape? If so, there are a few free trackers on my Contextures website, to give you some Excel help in reaching your goals.
With Excel Data Validation, you can add rules to a data entry sheet, and control what people put in the cells. In today's example, we'll set up a cell that only allows you to enter a weekend date. Just remember that Data Validation isn't foolproof, and people can find ways around your rules.
One of my Excel sample files has a data entry sheet, with buttons to add/update database records that are stored on a different sheet. I've just added a new version of the file, that helps you remember to save your changes.
December is only 2 days away, so it's time for an Excel Advent Calendar! I've made two new versions this year, and they don't use macros, just basic Excel features. Even if you don't need an Advent calendar, take a look to see how they're set up . You might find a use for these techniques in other projects.
Happy Thanksgiving, if you're celebrating tomorrow! Here in Canada, we have Thanksgiving in early October, when we're less likely to have weather-related travel problems. This will be a short post, with a links to a couple of Excel holiday planner tools.
To use VLOOKUP, the value you're looking for has to be in the first column of the lookup range. But what if your lookup table has Scores in column 3, and you need a description from column 2? Here's how you can do an Excel VLOOKUP to the left. Continue reading "Excel VLOOKUP to the Left"
In Excel 2013 and later versions, each file opens in a separate window. Unlike earlier single-window versions, there is no Exit button or command, to close all the files, without clicking each window individually.