5 Excel Secrets That Will Make Your Boss Think You’re a Pro

This year, Spreadsheet Day falls on Saturday, Oct. 17, a day in which number crunchers worldwide gather around the formula bar, fill their cells with all sorts of values, and reminisce about the 1979 birth of VisiCalc, the Apple II computer program that first brought this wonder to the workplace.

Of course, everyone has their own holiday traditions. Google Sheets users, for instance, like to get together with friends and collaborate on spreadsheets together. Apple Numbers users decorate their documents with multiple, multi-color charts and graphs. And OpenOffice users cobble together meager tables while talking about the good ol’ days of data sets.

Personally, I like to think Spreadsheet Day is all about family. But Excel users are the biggest bunch of spreadsheet revelers out there, and for them we have rounded up a list of five quick tips for getting the most out of their electronic ledgers this year.

1. Navigate Like A Pro

Sure, mice and trackpads are tools of the trade. But if you want to rock a Excel like an electric guitar, you’ve got to do your finger picking with the keyboard. For instance, holding down control while tapping an arrow button will 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 five 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

I know saying this makes me sound like the Grinch Who Stole Spreadsheet Day, but: Data entry is the worst. Cutting and pasting is fine; importing is great; but manually keying in values can crush the soul. Thankfully, starting with Excel 2013, 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. (This trick is an HR department holiday favorite!)

“For some people, business users and consumers alike, if you show them Flash Fill, you’ll get a standing ovation,” says Michael Pamphlet, a Microsoft Office product marketing manager.

5 Horrible Habits You Need to Stop Right Now

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

Pie charts on Spreadsheet Day are like pumpkin pie on Thanksgiving — of course they’re going to be there, but wouldn’t you rather have something else? In fact, Excel 2016 is serving up several refreshing new data visualizations to make your spreadsheets burst with flavor.

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. “This is a really interesting chart because it’s showing different type of information with your data,” says Pamphlet. “It’s showing you flow and contributions to a total, and this is something that’s useful for financial data.”

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. “We’ve made it super easy to create forecasted data based on historical data that you’ve created inside Excel,” says Pamphlet.

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. And the timing couldn’t be better, because now you have the tools to determine how many people will come to your Spreadsheet Day party next year.

Top 10 Tech Product Designs of 2014