Create a Rolling Total in Excel

Create a 12 Month Rolling Total

It's easy to create a running total in Excel, and show the accumulated amount in each row. You can use the SUMIF function in a table, or show running totals in a pivot table.

But what if you want to show the total for a specific number of previous months – not all the previous months?

(adsbygoogle = window.adsbygoogle || []).push({});

Continue reading Create a Rolling Total in Excel

Hide Formula Results Based on Date

Hide Formula Results Based on Date

My challenge this week was to show the running total in a list, but only for the current date and earlier. I wanted rows for future dates to appear empty, but have the formulas in them – ready to go. How would you solve that problem?

(adsbygoogle = window.adsbygoogle || []).push({});

Continue reading Hide Formula Results Based on Date

Count Items Based on Another Column

Count Items Based on Another Column

How can you count items in one column, based on a criterion in a different column? We've shipped orders to the East region, and want to know how many orders had problems (a problem note is entered in column D). COUNTIF sounds like the right function to use, but it doesn't work for this problem.

Continue reading Count Items Based on Another Column

Option Button Scores With CHOOSE Function

Option Button Scores with CHOOSE Function

You can use option buttons on a worksheet, to make it easy for people to pick just one item from several options. There's no programming required – just set up a group box, add option buttons, and link the buttons to a cell. The video at the end of this post shows you those steps.

The option buttons send a value to their linked cell, and we'll take a look at how you can use those numbers.

Continue reading Option Button Scores With CHOOSE Function

Beware the Nested IF Formula in Excel

Beware the Nested IF in Excel

Do you use the IF function in your workbooks?  It's helpful if you want to check something, and then show a result based on the result of your test.

But, things can go horrible wrong if you try to do too much with IF -- sometimes other functions do a better job.

Continue reading Beware the Nested IF Formula in Excel

7 Ways to Sum in Excel

7 Ways to Sum in Excel

What was the first function that you learned in Excel – if you can remember that far back?

I'll bet it was the SUM function. There's an AutoSum button on the Ribbon that makes it very easy to put a total – just select a blank cell below a column of numbers, and click that button.

Continue reading 7 Ways to Sum in Excel

Compare Excel Worksheet Values and Formulas

Compare Cell Values and Formulas in Excel

If Excel sheets are set up identically, you can create 3-D formulas, to sum a specific range, in a group of sheets. (You can use other functions in a 3-D formula too, such as Average, Count, Min or Max.)

For example, this workbook has sheets for the East, Central and West regions, and all three sheets are set up the same.

Continue reading Compare Excel Worksheet Values and Formulas

Counting in Excel

7 Ways to Count in Excel

The most popular function in Excel is SUM, based on my experience. That's probably the first function you learned how to use, and in the old days, it was the only function that you could insert with the appropriately named AutoSum button.

Sometimes we overlook how much counting we have to do every day too. Here are a few scenarios that you might encounter at work (or should I say "en-count-er"?):

Sales: number of orders for a specific product, from a list in a worksheet Human Resources: count of employees with 10+ years employment Shipping: number of shipments pending,

Continue reading Counting in Excel

How to Count in Excel

Learn 7 ways to count in Excel, with function tips and tricks

There are lots of different ways to count things in Excel – maybe you need to count the numbers in a column, or all the data, or just the blank cells. Fortunately, there is a function for each of those:


For example, to count the blank cells in the range A1:A5, use the following formula in cell A7:


More Complicated Counting

If you have more complicated things that you need to count, there are other functions to do the job:


For example, to count only the visible numbers, after filtering and/or

Continue reading How to Count in Excel

Ignore Outliers with Excel TRIMMEAN

Excel TRIMMEAN function

From what I've seen in workbooks over the years, SUM is the most frequently used Excel function, and AVERAGE is the runner-up. Would you agree, or do you see other functions used more often than those two?

Last week, a client asked about excluding some of the highest and lowest numbers from a data set, to give a better average. It looked like some of those outliers had been incorrectly entered (an extra zero, or a mistyped numbers), and they were throwing off the results.

So, we did some tests with the TRIMMEAN function, to compare the results with a

Continue reading Ignore Outliers with Excel TRIMMEAN