5 Awesome Excel Tips That Will Make You Look Like a Pro

Microsoft Excel can do anything from managing your office accounts to managing the data required for running a whole county, you just need to know how to use it.

Improving your Excel skills is a great way to save time on projects, especially because it’s a tool used by most of us on a regular basis. Even one little tip can help to save you time, every day.

Time Magazine writer John Patrick Pullen walks us through a few tips to make sure our Excel game is ‘on point’.

1. Navigate Like a Pro

Sure, mice and trackpads are tools of the trade. But if you want to rock Excel like an electric guitar, you’ve got to do your finger picking with the keyboard.

For instance,

– hold down control while tapping an arrow button to select every cell all the way to the end of the sheet.

– Flip through worksheets by holding control while pressing either page down or page up.

– Turn a range of numbers into currency, complete with a dollar sign and two decimal places, by pressing control, shift, and $ at the same time.

– Similarly, you can turn a number into a date (day, month, and year) by pressing control, shift, and # simultaneously.

These are just a few of the hundreds of keyboard shortcuts available in Excel. But don’t trash your mouse yet—it will come in handy for scrolling through all those navigational tips.

2. Flash Fill Your Data

Data entry is the worst. Cutting and pasting is fine; importing is great; but manually keying in values can crush the soul. Thankfully, the Flash Fill feature has helped ease this pain by examining the information you’re inputting and suggesting values in subsequent boxes. That might sound like magic, but it’s really just logic.

For instance,

– if you’re entering a column full of dates, Flash Fill will detect that and help Excel cells make that cognitive leap.

– Or, say you’re creating a contact sheet and have just filled a column full of email addresses, and you start a new column for last names. Once Flash Fill catches on, it can examine the email addresses and suggest the names for you.

3. Think Fast with Quick Analysis

For the experienced and uninitiated alike, Excel is a deep program with a variety of capabilities. But finding those tools can be one of the bigger challenges of using the program.

Quick Analysis is a small menu located at the bottom right hand corner of a table or list in Excel 2013 and onward, that pulls together the basic things that most users do with similar data sets.

So instead of having to go back to the ribbon and find the feature, you just click on to the corner of the table. And because it’s in such close proximity to your data, your mind won’t start wandering through all the other features of Excel, or to your web browser where you’re searching for help, or to Facebook because you just opened your web browser.

In addition, the contextual tools that Quick Analysis brings up can add great visuals and depth to your spreadsheet, making you look like much more of an Excel pro.

4. Show Off with New Charts

Excel 2016 is serving up several refreshing new data visualizations to make your spreadsheets burst with flavour.

The first two are hierarchical charts: the treemap and the sunburst.

The treemap is a giant square (that represents 100% of the values) full of rectangles (that represent smaller portions of the whole).

The sunburst functions similarly, though its round and the inside portions are like chunks of onion, rather than pie slices.

These two charts are great for showing how categories can add up to a whole, and even how smaller bits within those categories can contribute as well.

And a third new chart, the waterfall, is something that master spreadsheet artists have been laboriously building on their own for some time, but now can do with with just a click from the Insert>Charts menu.

5. Look Ahead with One-Click Forecasting

“Forecasting” is one of those terms that’s thrown around a lot in the age of Big Data, but even small spreadsheets have the ability to look ahead and make solid projections.

A new feature in Excel 2016, users can select columns on a table and have the spreadsheet make a few different projections as to how the results will play out in the future.

Displaying the information as either a line or bar chart, Excel will even calculate lower and upper confidence bounds (based on a programmable confidence interval) to show the possible margin of error. You can also fine tune the tool to better meet your needs, calculating for seasonality, for instance.

CTO offers training course that can be specifically tailored to meet your needs and requirements. If you’d like to find out more about our customised Excel courses, please click here or call us now on 1300 667 660.