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 […]
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 […]
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 […]
Joining together with CONCAT or TEXTJOIN.
Joining cells into one can be done in a couple of ways in Excel. The CONCAT function in Excel joins things together. CONCAT is short for concatenate. There is actually a concatenate function as well, and this does exactly the same thing, but it is an older function, and if you have a newish version […]
Switching it up with the SWITCH function
Switch is a useful function in Excel, that is a natural extension of writing about IFS last week. While it can handle up to 126 pairs, there are far better options if you have that many! SWITCH is probably a bit easier to understand than IFS but it only does exact matching (there is a […]
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 […]
A refresh on refreshing PivotTables.
Refreshing PivotTables – not just for updating the data. I just found out that sometimes you have to refresh the PivotTable to get all the columns of the source table to show in the PivotTable fields before you even start building it. On the subject of PivotTables, they are getting a long awaited update with […]
Get two Excels – Separate instances of Excel.
You can have more than one Excel open at the same time. Now I don’t mean two workbooks, but two separate instances of Excel. So, if one is busy running a macro, or updating a power query, you can have a completely separate instance of Excel open be working on that. Think about it like […]
Layers of Protection
As well as protect a workbook, or a worksheet, (as I have written about that here) you can also create layers of protection by using the Allow Users to Edit Ranges feature in Excel. This allows you to protect a range with a password. On the Review menu, next to the Protect Workbook button, is […]