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 […]
Even better data validation
Data validation is great, I have written about it here and here. And now it can be even better. It has always required a bit of mucking around to get the data validation list to grow automatically if you add more items to the list. I now have a new favourite way of doing it. […]
Expand
In the last few blogs I have written about some new functions in Excel. Expand is the last in this series and probably the most niche one of them all! =Expand(array, rows, [columns], [pad_with]) As you can see, you need to give two inputs, with two optional inputs as well. The array, is the group […]
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 […]
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. […]
Find out all about Find.
Most people know about Find. You can use it in most computer applications, word, excel, web browsers. When you use Find in Excel, there are a few features to be aware of to help you get the most out of your search. First – what are you trying to find? You can enter a word, […]
Why make a calculator in Excel?
Making a calculator in Excel for your business is a great idea if you have any process that requires numbers to be input and standard calculations that need to be done. For example, working out selling price, given a certain gross profit percentage; or calculating a commission based on what type of item was sold. […]
Connections to a folder – the magic that lets you connect to spreadsheets without using filenames!
A lot of people know that you can link one spreadsheet to another just by using a simple formula. What most people don’t know is that you link one spreadsheet to another using Power Query (a function within Excel). And even fewer people know that you can create a link to a folder and Power […]
Set print area, and add to it.
You don’t have to print everything on the excel sheet. You can set the print area, to print just the parts you want to. These sections do not need to be continuous. You can either select the different sections, while holding down the Ctrl key, or use the Add to Print Area button, see below […]