5 Clever Time-Saving Hacks in Google Sheets and Excel

time saving Excel tips in Excel / Google Sheet

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!

1. Transforming data into an interactive database

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.

【Table Formatting in Excel】

Excel Table Formatting

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.

【Table Formatting in Google Sheet】

Google Sheet Table Formatting

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)

2. Simplifying complex data for reports with Paste Special

Without using complicated formulas, you can automatically simplify data in a worksheet using the Paste Special command.

【Summarise the data from full numbers to millions of dollars in Excel】

Rather than dividing each cell manually by 1,000,000, use Paste Special!

Follow these 4 steps:

Excel Paste Special

1】First type the amount you want to divide the data by and then right click and copy the cell.

Excel Paste Special

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.

Excel Paste Special

3】In Operation, select the function you want to use. Here you want to divide all data by the one million you copied earlier.

Excel Paste Special

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!)

3. Locking a cell when copying formulas with absolute references

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.

【Absolute references in Excel & Google Sheet】

Excel Google Sheet Curreny Exchange

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.

▶ [B3*$B$12]

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.

4. Turning copied data into columns (Text to Columns)

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.

【Text to Columns in Excel】

Excel Text to Columns

1】Select your data range and then go to [Data] > [Text to Columns]

Excel Text to Columns

2】If your data is divided by spaces or commas, choose “Delimited”

Excel Text to Columns

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

Excel Text to Columns

4】Your data is now in columns, ready for further work.

【Text to Columns in Google Sheet】

Google Sheet Text to Columns

[Data] > [Split text to columns]

Choose the specific [Separator] on how to divide your data. For example, you could choose Space / Detect automatically.

5. Combining data using &

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.

【Combining text cells in Excel / Google Sheet】

Excel Google Sheet Combining data

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.

Excel for Marketers Course Learn faster, learn smarter with <1 minute bite size tutorials. No more long, boring courses. Everything you need, in a fraction of the time.

——

Editor's Pick:

Business Casual VS Smart Casual: What's the Difference?

10 Useful Tools for Digital Marketing (FREE!)

8 Necessary Project Management Tools

James Lewis

Vanna co-founder and employer branding expert

3 min read

Related tags

Suggested for you

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...

Should you listen to music when you work? Will it be a distraction, or the ultimate productivity boost? We looked at different studies to find out mor...

How to create a Gantt chart? Is TaskWorld better than Trello? To manage workflow as well as teamwork, project managers need agile tools to achieve eff...

15Jobs
Large · Food Tech · Sheung Wan
16Jobs
Large · Information Technology · Quarry Bay
9Jobs
Large · Law · Central
2Jobs
Small · Consumer Electronics · Sheung Wan