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, […]
Spilled range as chart source
Using a spilled range as a chart source will now automatically update the chart when the source changes. Another good reason to embrace these ‘new’ formula in Excel. You can read more about one of these formulas here Below is a bar chart with the data source next to it. The data source is the […]
Line up – Use Align to get your graphs layout just right.
When you have graphs (Charts) or pictures in Excel and you want to get them lined up you can use Align. It is available on the Page Layout Menu (Alt, P AA) and also the Format Shape Menu. (Alt, JA, AA) You need to select the elements first. Use the Shift (or Ctrl) key (hold […]
Displaying Numbers just the way you want.
Displaying numbers in a cell is an important part of making your data easily readable. The way a number is displayed in a cell can be different to the data that is stored in the background. You can change the way the data looks by formatting the cell. Use the Number section on the Home […]
How to fit in… your data into a cell.
Fitting data into a cell can be tricky, but with a few simple tricks and tips, you can fit the data into the size and shape of cell that you want. Resize your columns. You can change the width of an individual column (or row height) by moving your mouse pointer to the line between […]
Powerful Filter Button Options.
Once you have added Filter buttons to your data, either via the home menu or because you have a table, you have a lot of powerful options on how to use them. Filter by Color – if you have colours on your cells, either from formatting or conditional formatting, you can use this to filter […]