Transpose – switch it two ways.
The transpose function rotates an array of data. So, changes from data going across a row to down a column and vice versa. There is also a transpose under paste special, which does the same thing. In this example, we can see the formula is referencing row B. From B2 to B7. The result of […]
Being choosy with the CHOOSE function
The CHOOSE function in Excel allows you to bring back a range of cells, based on a single input. =CHOOSE(index_num, value1, [value2], …) The minimum is two inputs, the index number and a value. It wouldn’t be a very interesting function if that is all it did! The power comes when you add other values, […]
Subtotal function, more than meets the eye.
If you have ever added a total to a table you might have noticed that it doesn’t use a SUM function, but a strange thing called SUBTOTAL (And if you never have added a total to a table, then you really should! You can read about the greatness of tables here). So, why does the […]
Working days made easy.
You can work out the date so many days away in Excel, by just doing one date plus the number of days. This works because Excel treats dates as numbers (as long as you have actual dates, and not just text that looks like a date). However, if you are trying to calculate with working […]
DAY, MONTH, and YEAR functions in Excel.
There are formulas in Excel to take a date, and return the day of the month, the month of the year, or the year itself. Of course, you can also format a date, so it stays as a date, but only displays the day, month, or year, but sometimes you need just the number and […]
Show as text with TEXT function
The TEXT function allows you to show a number (or date, or some other value) as text. But why would you want to do that? Well, sometimes you want to join numbers with text and that only works if both of them are text, so we change the number to text to join it. In […]
Trim, clean, and proper.
Cleaning your data is often an important pre step to actually using it to make decisions. Three useful functions in Excel to help do that are TRIM, CLEAN, and PROPER. TRIM(text) The TRIM function will remove all spaces from text except for single spaces between words. You would be surprised how often people put extra […]
Get text from text – using Excel text functions.
Excel has a lot of functions that can deal with text. The first three I will introduce you to are Left, mid, and right. These are functions in Excel for pulling bits of texts out of other texts. For example, if you had an address that has the post code on the end, after a […]
Scroll left and right!?
You can scroll sideways using the mouse wheel. Just hold down Ctrl + Shift and start scrolling. Down goes right, and up goes left. Remember you can also use Ctrl and the arrow keys, and end and home to jump around your spreadsheet much quicker than scrolling too. Ctrl and arrow keys are one of […]
Search and Find – same same, but different.
Search and Find are two similar functions in Excel. Their syntax is the same (find_text,within_text,[start_num]) Basically, what do you want to find, what text are you looking in, what number character to start at. The first two inputs are required, the third is optional. If you don’t specify then it will start looking at the […]