We're trying out a new weekly update - 3 Tip Friday, where every week you'll receive a short update from us with just 3 simple tips for improving your use of Excel. This week we start with 3 easy ones!
A quick win you can learn today -
We all know that you can easily change the font size, face, or colour of a cell... but did you know you can have multiple font types within a cell? Double click into any cell, and use your mouse to highlight the text you wish to change. Use the formatting options to change the format of the text, and you will discover that you can change individual words and characters within your cell. You can use this to make a specific word bold or stand out in a different colour.
A keyboard shortcut I'm adding to my list -
Copy and paste using shortcuts is easy enough (just Ctrl + C to copy and Ctrl + V to paste)... but what if you want to 'fill' instead of just paste? If you have a date, and you want to fill it down 10 cells, increasing the date one day at a time, you can drag the bottom right of the cell down to achieve this. But did you know you can also do this using the 'fill down' shortcut Ctrl + D?
Using Ctrl+D will copy from the top cell in the range you select, and fill down the range using autofill, rather than just pasting or duplicating whatever is in the top cell.
Give it a try - I find it a super useful shortcut.
A function I've found super useful this week -
A client of mine wanted to reconcile two spreadsheets from the old and new versions of a marketing system, to make sure all the entries were present in both lists and matched exactly... but there was one problem. The new system had exported data by replacing all the spaces, full stops, commas, and special characters with underscores... and the old data still had spaces.
So before I could use VLOOKUP to compare the data sets, I needed to fix the data with the underscores. I did this using the SUBSTITUTE function - which allows you to substitute any character or word in a cell with another. It's great for doing mass 'Find and Replace' actions on big data sets!
Just type =SUBSTITUTE( and add the following components:
1.The cell where you wish you substitute text.
2.The old text (in this case this would be an underscore, surrounded in speech marks "_")
3.The new text (in this case a space, " ")
4. Optionally you can add a number to the final part of the function to indicate which instance of the character you wish to substitute, but leave it blank to substitute all.
Hoping you find these updates useful - let me know what you think.
p.s. I’m super excited our new Excel course, which is launching later this month. Stay tuned for more details.