Show as text with TEXT function
The TEXT function allows you to show a number (or date, or some other value) as text. But why would you want to do that? Well, sometimes you want to join numbers with text and that only works if both of them are text, so we change the number to text to join it. In […]
Trim, clean, and proper.
Cleaning your data is often an important pre step to actually using it to make decisions. Three useful functions in Excel to help do that are TRIM, CLEAN, and PROPER. TRIM(text) The TRIM function will remove all spaces from text except for single spaces between words. You would be surprised how often people put extra […]
IFERROR and IFNA – what to do with errors.
Last week I wrote about IF. There are three other formula that start with IF, I will cover two of those here, IFERROR and IFNA. IFERROR IFERROR is a very helpful function. You can use it to sit in front of another function and deal with errors before they become a problem. For example, if […]
Counting is hard….
Counting can be tricky. It is not always obvious what you are counting, what counts and what doesn’t? In Excel, there are several options of functions for counting, so you can get just what you are looking for. =COUNT(value1, [value2], …) is the first option you might choose, but it is not the most generic. […]
What a difference a t makes… Sept or Sep?
Sept or Sep? Which looks better? Which is consistent with the other months? Who decides which is “correct”? Microsoft has made an update to the way months are displayed. Well, one month actually. But only if you are in certain places in the world, or at least your computer settings are set to that location. […]
Finding the middle
Excel has many ways for you to find the middle point to your data. The three most common are average (what I would call mean), median, and mode. Now I will try not to go all maths teacher on you here, but a quick refresh on each of these. Average is what you get when […]
Take it to the Max with Min and Max
Min and Max functions in Excel are used to finding the smallest (or biggest) number in a list. For that, you can use functions in Excel, Min (for smallest – minimum) and Max (for largest – maximum) These functions just can take one input, the range of cells that you are looking at where you […]
Python in Excel
I have just finished Python in Excel course by XelPlus! If you are looking for comprehensive online courses in Excel, then I recommend XelPlus. The videos are clear and thorough. There are worked examples to go through and extra help if that is required. I was skeptical about the need for Python, but I can […]
All about Replace
I wrote about Find a while ago, and thought I should add a bit here about replace as well. The real power of Find and Replace is the Find, so do go and check out that post if you didn’t see it. But Replace has its own tricks that are worth noting. The first is […]
Being choosy, choosecols and chooserows
CHOOSECOLS and CHOOSEROWS are two new functions in Excel. They do what they say. They allow you to choose columns or rows from an array. =CHOOSECOLS(array, col_num1, [col_num2],…) Input required is the array, and then the column numbers that you want to display. (At least 1 is required, note the square brackets meaning that input […]