Whether you've been using Excel for years, or just a few months, here are our 5 Excel tips that everyone should know.
Want to watch bite-sized videos instead? Enjoy 10 free Excel courses on Vanna Upskilling now!
Table formatting may seem like a simple tool, but it is one that few people take advantage of. Table formatting allows you to take your data range and turn it into an interactive database, making it easier to analyse data.
To set up a table, click on any cell in your dataset, and then select [Home] > [Format as Table].
Choose your colour scheme and then make sure the relevant data is selected and you have the box ticked if your data includes headers.
With your database ready, you can easily sort and filter data.
Select your data range, and then click [Format] > [Alternating Colors].
Choose your colour scheme and have the box ticked if your data includes headers.
For sorting and filtering data, click on arrow in specific column for further actions (eg. sort data from A-Z)
Without using complicated formulas, you can automatically simplify data in a worksheet using the Paste Special command.
Rather than dividing each cell manually by 1,000,000, use Paste Special!
Follow these 4 steps:
1】First type the amount you want to divide the data by and then right click and copy the cell.
2】Next, select the data range you want to simplify, being careful not to select the bottom row which sums the data. Right click, press Paste Special and choose paste special again.
3】In Operation, select the function you want to use. Here you want to divide all data by the one million you copied earlier.
4】Once your data has been simplified, you can change the width of the columns and delete the cell you copied. Remember to also change the units on the table.
(Note：Paste Special is currently not available in Google Sheet. Please refer to the next function!)
The “$” dollar sign in a cell reference affects just one thing - it instructs Excel how to treat the cell when the formula is moved or copied to other cells.
Here we have data that we want to convert to another currency. Using a simple formula, we multiply the original currency by the exchange rate.
However, when we try to copy that formula to the rest of our dataset, Excel automatically updates all parts of the formula.
We can use the $ sign before the row and column coordinates to lock the cell reference.
Then, when we copy the formula, the locked cell reference does not change.
You can use this function in any situation where you need to multiply or divide numbers by one specific cell, as well as many other uses.
If you copy a block of data from a webpage, a word processing file, or other text file, all the data is dumped into a single column of cells.
To make this data usable, you need the text to columns function.
1】Select your data range and then go to [Data] > [Text to Columns]
2】If your data is divided by spaces or commas, choose “Delimited”
3】Excel will automatically choose how to divide your data. If this looks correct then click next and check the formatting of the data is also correct before clicking finish
4】Your data is now in columns, ready for further work.
[Data] > [Split text to columns]
Choose the specific [Separator] on how to divide your data. For example, you could choose Space / Detect automatically.
Combining cells is easy when they’re numbers, you simply add the cells together.
However, when you have text to combine, you need to use the ‘&’ function between the cells you want to combine.
For text, if you also want a space between the combined cells, then insert a space between two quotation marks, followed by another & symbol.
▶ [A2&“ ”&B2]
You can use this function to add any number of cells together and quotation marks to add spaces or set text between the combined cells.
Ready to gain new skills and accelerate your career with reputable online courses? From digital marketing to self-development and more choices, start ...
Studies show that we spend on average over 6 hours a day checking emails. Unnecessarily long emails are unproductive and waste a lot of our energy, wh...