The new year has arrived, and with it comes the opportunity for improvement — especially in Microsoft Excel.
Whether you’ve been using Excel for years, or just a few months, chances are that you could learn something new to help you save time.
Ready to get started? Here’s our list of the top five time-saving Excel tips for 2018:
We’re guessing that you probably know about the all-powerful Ctrl + Z, which can undo pretty much any Excel mistake you’ve just made. But there are so many more underused shortcuts in Excel that can speed up your workflow in a major way.
The next time you working in a spreadsheet, resist the urge to reach for the mouse, and instead take the time to learn at least some of these useful commands. Even if you master only one shortcut per week, your Excel skills will improve dramatically within the year.
Here are a few to get you started:
Shortcut What it Does
Ctrl + A Select the current area; twice to select the current sheet
Ctrl + Space Select the current column
Shift + Space Select the current row
Ctrl + 0 Hide the current column
Ctrl + 9 Hide the current row
Ctrl + : Enter the current date
F4 Apply absolute refs (in a formula)
Alt + H, B Add border to cells
Excel’s Autofill and Flash Fill options can save you hours of data entry, but so many users are not even aware that they exist. Autofill can automatically fill out incremented numbers and formulas in columns and rows in a fraction of the time it would take you to do it manually. Flash Fill can automatically fill out other data, such as first name or last name, using the information that you have.
To activate Autofill, simply click and hold the lower right-hand corner of the cell, and then drag the mouse down the column or across rows. On the bottom right of the column or final row, click on the box that says “Auto fill options,” and then click on “fill series.” You can also create your own series in File/Options/Advanced/General/Edit Custom Lists.
Flash Fill is on by default and will work if you type data that matches a pattern. For example, say that you have set up columns with “first name,” “last name,” and “first and last name.” After you fill in the first row of the “first and last name” column, Flash Fill can fill in the rest by using the data from the first two columns. If this doesn’t happen automatically, click Data>Flash fill or Ctrl + E.
It’s not easy to collaborate on Excel. When numerous people are working on the same document, it can take many hours to combine and verify the data. In such cases, it’s also very easy to make mistakes, and one tiny error can be catastrophic.
Excel horror stories abound: For example, the infamous tale of the London 2012 Olympics snafu, when 10,000 additional tickets to the synchronized swimming event were sold by mistake because of a spreadsheet error. Or another small Excel error in 2014 that resulted in Tibco Software receiving $100 million less than it had originally expected.
A tool such as DataRails, which automatically consolidates data, tracks all revisions to Excel files and flags unusual activity, can be an easy and powerful way to save you lots of time and money. Make 2018 the year that you take control of your spreadsheets so you can breathe easier knowing that your data is accurate.
If you are manually entering figures from a web page onto your spreadsheet, then you are wasting a lot of time.
Excel makes it incredibly easy to import data from the web. If you happen to find figures on a web page that would be useful to you, convert it into a spreadsheet. Simply click Data > From Web and when prompted, copy and paste the URL. That’s it! You’ve just saved yourself hours of work.
Make this the year that you get to know PivotTables, another extremely powerful but underused Excel tool. PivotTables allow you to easily reorganize and summarize your data in various ways. Doing so can help you answer various questions and even help you discover new trends. For example, you can use PivotTable to calculate sales for customers by product, and in a click, you can see the sales of products by month.
Check it out under Insert>PivotTable.