Excel tips and tutorials
If you’re trying to figure out when a project will be completed, you can use the WORKDAY.INTL function (Excel 2010 or later). It’s more flexible than the older WORKDAY function – it doesn’t assume that you work Monday to Friday any more!
Continue reading “Customize Weekends With Excel WORKDAY Function”
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?
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?
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”
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).
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”
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.
For example, in the screen shot below, the total won’t show if a quantity hasn’t been entered in cell D7.
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.
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”
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”?):
Fortunately, your favourite spreadsheet can help – there are many different ways to count things in Excel.
