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…)Avoid the #DIV/0! Error in Excel
If you have the subscription version of Excel you can create your own functions. One that you may want to create avoids the #DIV/0! error. (more…)
Free Webinar Recording – Excel Formatting Tips
In October 2019 I re-ran my Formatting Tips session. The detailed pdf manual and example file can downloaded by using the button below. Content listed below the video. Download Materials CPD note - if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances. https://vimeo.com/366395923 This session covers: a format to avoid and the one to use in its place keyboard and mouse…
Custom List Trick
It is common to use Q1 for quarter one. Excel will even cycle through Q1,Q2,Q3 and Q4 when you drag a cell contain Q1. What if you want to use the sequence M1 to M12 for months? Custom Lists to the rescue! (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.
Excel Data and Formula Entry Tips
There are number of shortcuts you can use to speed up your data and formula entry in Excel.
Number keypad
Use the number keypad on the right of the keyboard. This has all the numbers, as well as most of the formula operators (+ * – /), you need to create formulas. It also has a large Enter key. The numbers are laid out like a calculator and so are easy to use. (more…)
Handling Text in Calculation Cells in Excel
If you need to do mathematical calculations on cells that may contain numbers or text, then you can use the IFERROR function to ignore the text. (more…)
Handling Zeroes in Excel
It’s amazing how passionate some people can be about zeroes. I have known people who hate, with a passion, to see zeroes displayed in their reports. They will sometimes use some formula acrobatics to avoid having a zero displayed. (more…)