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, […]
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 […]