Excel tips and tutorials
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?
In this week’s roundup, find errors, lock slicers, plan for the holidays, use keyboard shortcuts, and much more.
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?
In this week’s roundup, prevent duplicate totals, build an Excel dashboard, formatting nit-picks, and much more.
Do you use Excel’s Subtotal command to automatically create subtotals in a list? I prefer pivot tables, but still use Subtotals in a few macros for clients. Last week, one of them asked why there were duplicate Grand Totals after applying two levels of subtotals.
Continue reading “Excel Subtotals With Duplicate Grand Totals”
In this week’s roundup, combine data, select multiple items, power query tips, better reports, and much more.
If you select an item from a data validation drop down list, it’s entered in the cell, replacing any existing data in the cell. If you’d like to add more items to the cell, instead of replacing the current text, you can use a bit of programming, to allow multiple selections from a data validation list.
In my sample file that shows how to do this, I’ve added new code, to prevent a problem that occurs in one type of situation.
In this week’s roundup, delete sheets, hide passwords, use Boolean logic, and many more tips. I hope you’ve recovered from your Spreadsheet Day celebrations, and are ready to start a new week.
Happy Spreadsheet Day 2015! Thanks for stopping by, and please vote in the survey below, for the Spreadsheet Hall of Fame.
We celebrate on October 17th because that is the date on which VisiCalc was first released, way back in 1979 – and that was 36 years ago. How time flies when you’re having fun with spreadsheets!
How can you count items in one column, based on criteria in a different column? We’ve shipped orders to the East region, and want to know how many of those East orders have a problem note in column D. Instead of the COUNTIF function (1 criterion) we’ll use the COUNTIFS function (multiple criteria).
