Excel has a lot of built-in number formats, but sometimes you need something specific. Whether you’re representing a little-used currency, tracking in-stock units, or want to color code profits and losses, you are in need of a an Excel custom number format. Number formatting in Excel is pretty powerful but that means it is also somewhat complex. This is the definitive guide to Excel’s custom number formats… Continue reading The Definitive Guide to Custom Number Formats in Excel
Category Archives: Definitive Guides
The Definitive Guide to Excel Error Types and Error Handling
Excel functions like VLOOKUP, MATCH, and INDEX are great tools, but when they don’t work, they throw errors that can break an entire spreadsheet if referenced incorrectly. Error messages can be especially bad if they show up on end-user worksheets like reports and dashboards. Fortunately, Excel has a way to catch errors like #VALUE!, #NUM!, and #REF! before they show up. Learn how to handle error messages in Excel here… Continue reading The Definitive Guide to Excel Error Types and Error Handling
The Definitive Guide to Using Dates and Times in Excel
Dates and times are two of the most common data types people work with in Excel, but they are also possibly the most frustrating to work with, especially if you are new to Excel and still learning. This is because Excel uses a serial number to represent the date instead of a proper month, day, or year, nevermind hours, minutes, or seconds. It’s made more complicated by the fact that dates are also days of the week, like Monday or Friday, even though Excel doesn’t explicitly store that information in the cells. Here is the definitive guide to working with dates and times in Excel… Continue reading The Definitive Guide to Using Dates and Times in Excel
SUMPRODUCT Fundamentals – Multiply Without Adding Clutter
Not every function has to be complicated in order to save time and clean up cluttered worksheets. SUMPRODUCT is a basic function that combines the uses of PRODUCT and SUM (brush up on the SUM formula here) to save space and improve readability of your models and dashboards. This quick tutorial will show you how it works… Continue reading SUMPRODUCT Fundamentals – Multiply Without Adding Clutter
SUMIF Fundamentals – Better Totals and Conditional Sums
For basic tallying and totaling, the SUM function is a pretty good workhorse (you can review SUM function basics here), but often more control is needed. When you have a mixed list and need to add up only a certain type, SUMIF and SUMIFS be enormously powerful tools. At its most basic, SUMIF lets you pick which cells in a row or column to include in the sum, based on a defining characteristic. Let’s explore how it works… Continue reading SUMIF Fundamentals – Better Totals and Conditional Sums
SUM Fundamentals – Make It All Add Up
The SUM function is a simple but important one. It forms the basis for a number of powerful and sophisticated tools including SUMIF and SUMIFS that we will explore later. You have to walk before you can run, though, so let’s take a look at the fundamentals of the SUM function… Continue reading SUM Fundamentals – Make It All Add Up