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 […]
TAKE and DROP
The new functions TAKE and DROP allow you to pick from the top or the bottom rows (and columns) of a group of cells. If you have a sorted list and only want to see the top of your list, this is easy with TAKE. In the picture below we have a list of players, […]
Stack your data with VSTACK and HSTACK.
Here are another two new functions in Excel: VSTACK and HSTACK. The V is for vertical, and the H is for horizontal. The STACK part implies we are making a stack or pile and that is exactly what we are doing. The input is two, or more, arrays. These can be groups of cells, as […]
TOCOL and TOROW functions.
Excel is constantly being updated. TOCOL and TOROW are two new functions. TOCOL TOCOL takes the input array (just a fancy name for a group of cells) and puts them all in a column. TOCOL(array, [ignore], [scan_by_column]). As you can see, the TOCOL function takes the input cells and stacks them all into one column. […]
Automatic Data Conversion, Helpful?
When you paste data into excel, or open a csv file in Excel it “helpfully” does an automatic data conversion on data that it deems needs it. This can be great, it that is what you want. But it is very annoying if it isn’t! It even changes what you type into a cell, for […]
Trim range function and the dot!
You can now reference a whole column, but not the whole column in Excel and all with a dot which stands in place for the TRIMRANGE function. That might be a confusing statement, so let me explain. See the picture below. In both, I have referenced the whole of column Q. In the left-hand picture, […]