In a perfect world, nobody would ever make a mistake in Excel. Every formula would be flawless, and every bit of data would be valid. Unfortunately, that's not the case. When mistakes happen, there are built-in errors, like #N/A, but why not create your own Excel formula error values, and show those too?
When you first start to use Excel, you probably work with files that somebody else set up. Eventually, you're ready to build a workbook of your own, and this video shows how to make an order form in Excel, starting from a blank workbook.
Someone asked me how to make a data validation drop down that only shows the visible rows from a filtered list. I created a sample file that shows how you can do that, and here are the details on setting up a drop down from filtered Excel list.
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.