Get text from text – using Excel text functions.
Excel has a lot of functions that can deal with text. The first three I will introduce you to are Left, mid, and right. These are functions in Excel for pulling bits of texts out of other texts. For example, if you had an address that has the post code on the end, after a […]
Search and Find – same same, but different.
Search and Find are two similar functions in Excel. Their syntax is the same (find_text,within_text,[start_num]) Basically, what do you want to find, what text are you looking in, what number character to start at. The first two inputs are required, the third is optional. If you don’t specify then it will start looking at the […]
Joining together with CONCAT or TEXTJOIN.
Joining cells into one can be done in a couple of ways in Excel. The CONCAT function in Excel joins things together. CONCAT is short for concatenate. There is actually a concatenate function as well, and this does exactly the same thing, but it is an older function, and if you have a newish version […]
A bit iffy… how to use IFS.
You can nest if statements inside each other, but just because you can doesn’t mean you should! A better option is to use IFS. This allows you to specify conditions and what you want to do when those conditions are met, in a way that is much easier than nesting if statements inside each other. […]
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 […]
IF – powerful word, just ask Rudyard Kipling.
If is a little word with a big impact. Likewise, the IF function in Excel is one of the smallest (in letters) of the Excel functions, but can be powerful, and is often a gateway to formula use. =IF(logical_test, [value_if_true], [value_if_false]) The IF function looks like it requires a minimum of 1 input, (note the […]
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. […]
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 […]