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 […]
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, […]
Big raps, Wrap functions.
Two new functions in Excel that can wrap your data are WRAPROWS and WRAPCOLS. They are basically the opposite of VSTACK and HSTACK. You can read about those here. WRAPROWS – wrap your data into a set number of columns. WRAPROWS will take a row or column of cells and spread them over the specified […]
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 […]
Text, text, text, three new text formulas.
There are three new text formulas in Excel. TEXTSPLIT, TEXTBEFORE, and TEXTAFTER. These allow you to do things easily that were previously difficult and often involved multiple nested formula. TEXTSPLIT TEXTSPLIT allows you to take text from one cell, and split it into multiple cells based on a delimiter. A delimiter is just a fancy […]
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. […]
Deal with duplicates
There are a few ways to deal with duplicates in your data. See your duplicates If you want to see where the duplicate values are, you can use Conditional Formatting. Found on the Home Menu, Conditional Formatting has heaps of easy to use options for all sorts of things. To highlight the duplicate values in […]