Yet Another Blank Cell Solution
When you link to an empty cell Excel it displays as zero. The same happens when you filter a list using the FILTER function. Blank cells display as zeroes. Here’s how to stop that.
(more…)Handling Formula-Based Blanks
It is common to display a blank cell using the IF function and “”. A problem can arise when you want to use that IF formula in a calculation. Here is an easy way to cope.
(more…)Updates to Pivot Tables
If you have the latest Excel version or the subscription version, you may have noticed some refreshing improvements to PivotTables. (more…)
Validating Checkbox Linked Cells
When you create a checkbox you need to link it to a cell on a sheet to be able to use its result. The user could overwrite that linked cell with a value or text and affect formulas that are using the checkbox linked cell. You can add a validation to make sure the linked cell only contains TRUE or FALSE. (more…)
Inserting a Blank Row Between Entries
Over the years I have had many requests to help people insert blank rows between entries is a list. Apparently it is for an input routine that requires blanks. My normal solution is a macro because it automates the process, but there is a manual technique that is quick and easy. (more…)
TRANSPOSE Function in Excel
The TRANSPOSE function is one of only a few functions that must be entered as an the array using keyboard entry Ctrl + Shift + Enter (CSE). It allows you to switch a range from going across the sheet, to go down the sheet and vice versa.
Populating Blank Cells in Excel
I have mentioned before that blank cells in your data can affect Pivot Table defaults in Excel. They can also reduce the effectiveness of some keyboard and mouse shortcuts. The macro below populates blank cells in the selected range with zeros.
(more…)Why Does My Pivot Table Default to COUNT?
When you drag a value column to the value section of a pivot table, sometimes the default calculation is COUNT, not SUM. This is frustrating, but easily fixed.
How to Identify Blank Cells in Excel
When developing formulas in Excel you often need to know when a cell is blank. There are different ways to check if a cell is blank, the one you chose will depend on what you are trying to achieve.