Excel has a few counting functions. But when it comes to counting entries in a cell it can be difficult if you are using formulas that return a blank cell. This is where the SUMPRODUCT function can come to the rescue.
Category Archives: Excel Blog
Distinct Count in Excel
The Data Model to the rescue
Counting is the poor cousin to summing in Excel. Not many people count things, but everyone adds up things. There is a special sort of count that can be useful. A distinct count counts unique entries and is hard to do with a formula. If you have Excel 2013 or a later version you can use a PivotTable to perform a distinct count.
First and Last Cell Address in a Range
Following on from my last two VBA posts here is how you can identify the cell addresses of the first and last cells in a range.
VBA to Find the Row and Column Number Limits of a Range
First and last row and column numbers
In my last blog post I found the last used row and column numbers on the active sheet. This post lets us find the row and column extremities of a specific range.
Excel VBA to Find the Last Used Row or Column
UsedRange used wisely
When writing VBA code it is common to need to know the last used row and/or the last used column. These values provide the edges of the sheet contents.
Controlling a Conditional Format with a Checkbox
Turn it off and on
Here is a technique that allows you to turn off and turn on the conditional format without actually removing the conditional format. You may want to do this to print a sheet without the conditional formats being applied.
Change in Ctrl Key Selections
Latest versions
Do you use the Ctrl key and the mouse to select multiple ranges? Well you may want to watch this short video.
Excel VBA to Remove Filters
Its a one liner
When creating macros that work with filters it is a good idea to remove filters at the beginning of the macro code. Here is how you do that.
Missing Sheet Gridlines in Excel
Its a format
Ever seen a sheet with what looks like missing gridlines? Wondered what happened? The issue is a format.
Updates to Pivot Tables
Time-saving improvements
If you have the latest Excel version or the subscription version, you may have noticed some refreshing improvements to PivotTables.
Filling a large range in Excel with a series [Video]
Keyboard and mouse shortcuts
Let’s assume you need to fill a vertical range with all the whole numbers from 1 to 100. There are lots of ways to do this, but I think the keyboard could be the quickest.
Running Total in a Formatted Table
Using the SUM function
Formatted Tables allow you to create formulas that automatically copy down as the table expands. To create a running total in a column you have a couple of options.
Creating a Timeline Chart in Excel 2016
Error Bars plus a new feature
Timeline charts are an effective way to display events over time. You can use a new Excel 2016 feature to easily create a timeline chart.
Auto Format Formulas in Excel
Excel 2013 onwards
Would you like to change the format of all your formula cells so they have a different fill colour or font? There is a way in Excel 2013 onwards.
Horizontal or Vertical Progress Bar in Excel
Conditional Format or Sparkline take your pick
In a recent webinar on conditional formatting I was demonstrating how to create a horizontal progress bar using conditional formats when someone asked an interesting question about creating a vertical progress bar. It is possible and in this blog post I will explain both techniques.
Range Names and Macros
Range names rule
When you record a macro that refers to a particular cell or range on a particular sheet in Excel the range reference is hard coded into VBA (macro) code. Unfortunately this means if rows or columns are inserted or deleted in the reference range the code is not updated. There is an easy way to get around this.
Related Tables and PivotTables
Don't Panic!
When you are building a PivotTable based on two related tables you may see unusual layouts that don’t make sense. Don’t worry, when you add values to the table all will be fixed.
Depreciation using the INDEX function
Alternative to OFFSET
Recently Liam Bastick (Excel MVP) wrote an article about using the OFFSET function to calculate depreciation in financial models. You can check out the full article here.
Adding a Vertical Dotted Line to an Excel Line Chart
An error bar solution
When plotting Actuals and Forecasts on a single line chart you may want to use a vertical dotted line to identify where the Actuals finish and the Forecasts begin.
Copying Without Affecting Relative References
Works for Excel 2013 onwards
There is an easy technique to copy a single formula and paste it without affecting relative references but what if you wanted to paste lots of formulas?