INTELLIGENT TECHNOLOGIES, INC’S NEWS & TIPS BLOG

Become a Spreadsheet Master with Five New Excel Tips for 2019

Laura-Square

Laura Schomaker, Marketing Associate

January 24, 2019

Because of its wide availability and relatively low learning curve compared to other data analysis tools like Power BI or Tableau, Excel remains one of the most popular software packages used by accountants, finance professionals and data scientists. So, for those of you who spend much of your workday inside Excel, I want to share a few of our favorite time-saving Excel tips and tricks to help you get more done in less time.

If you haven’t checked out our other blog post on the Excel, now is a great time to catch up. The five Excel tips and tricks we share there are just as relevant as they were when I wrote them in 2017.

Now on to the Excel tips for today. Below is a list of the tips we will be covering. If there’s one in particular that interests you, just click the link to jump right to it.

 

  1. The Quickest and Easiest Way to Create Pivot Tables
  2. Select Large Amounts of Data without Scrolling
  3. Get Summary Data without Using Formulas
  4. Create Tables and Charts in Seconds
  5. Can’t Remember that Formula? Build It!

Excel Tips #1: The Quickest and Easiest Way to Create Pivot Tables

Especially when you are new to pivot tables or have a lot of complex data to work with, creating them can be a real hassle. Even if you’re a pivot table Pro wouldn’t you like to get your reporting done faster?

Excel is now smart enough to recommend pivot tables that fit your data. All you have to do is go to Insert | Pivot Tables, highlight the data you would like to include and choose the pivot table you like best. It’s that simple. Excel will now automatically create your table, which you can use to manipulate your data until your heart’s content. See how it works with the visual tutorial below.

5 STEPS


1 Highlight the data you wish to analyze and click Insert

Step 1 image

2 Click Recommended PivotTables

Step 2 image

3 Choose a Pivot Table from the list on the left side of the dialogue box.

Step 3 image

4 Your Pivot Table opens in a new sheet. On the right side of the screen, you'll see a list of fields you can add the the table.

Step 4 image

5 Click the check boxes next to any you wish to add.

Step 5 image

Here's an interactive tutorial for the visual learners

https://www.iorad.com/player/166313/The-Quickest-and-Easiest-Way-to-Create-Pivot-Tables-in-Excel

Excel Tips #2: Select Large Amounts of Data without Scrolling

This one may seem simple, but it can really come in handy when you have a large amount of data in front of you. The easiest way to select an entire column in Excel is to select the first cell in that column or row and press Ctrl + Space bar. If you’d like to select a row instead of a column, highlight the first cell in the row and press Shift + Space bar

Note: this also works for selecting multiple rows and columns. Select the first cell in each of the rows or columns you’re interested in and use the keyboard commands I described above.

Excel Tips #3: Get Summary Data without Using Formulas

Once you’ve used the tip above to select the range of data you’re interested in, just look at the bottom of your screen. There you will find a variety of summary data like average, some and count, all calculated automatically for you without the need for any formulas.
 

Summary data automatically calculated in Excel

Excel Tips #4: Create Tables and Charts in Seconds

This tip is another handy keyboard shortcut. Creating tables and charts in Excel is easy but takes several clicks. If you’re in a hurry to visualize your data with charts and tables, here’s the quickest way to do it.
To create a table, highlight the data you would like to include and press control + T on your keyboard. In an instant, a table will appear displaying your data. If you want to adjust the look of the automatically created table, all you have to do is select the table tab in the Excel ribbon and use the controls it offers to make any changes.
Creating a chart in a flash is very similar to the method I described for creating a table. This time, instead of pressing control + T, you’ll press Alt + F1. Again, if you want to make any adjustments to the chart that appears, you can do so by selecting the chart and using the design options that appear in the ribbon.

Excel Tips #5: Can’t Remember That Formula? Build It

The formulas available in Microsoft Excel give you endless options for data manipulation and calculation. However, unless you use the same formulas repeatedly, it’s difficult to remember the exact commands needed for a certain calculation.
So, with Excel 2016, Microsoft introduced the formula builder. This handy tool makes writing valid formulas, quicker and less frustrating than ever before. First, select the cell where you’d like the formula result to appear. Then, to open the formula builder, click the formulas tab on the ribbon, then “Insert Function.” A new dialog box will appear on the right side of your screen. If you cannot see the entire thing, or its location is inconvenient, just click it and drag it to a new spot on your screen.
Once you’ve got the dialog box where you want it, you will see it lists all available functions in Microsoft Excel. You can scroll through them to find the one you need or type the name of a function to bring it up. Clicking on the name of any function will display a short description. Use this information to decide if the function you selected is the one you need. Once you find the right one, click “Insert Function.”
Then, the formula builder will ask you what data you want to use in the formula. You can select the data in one of two ways. One, you can type the data in. Or, you can click the cells that contain the data you would like to use. My preferred method is to click the cells rather than type. This allows you to avoid typos or syntax errors. And if you accidentally click the wrong cell, all you have to do is choose the correct cell and Excel will update the formula for you.
If you’re unsure what information the function you’re working with requires, click on the “More help with this function” link at the bottom of the dialog box and a detailed explanation will appear. See how it works with the visual tutorial below.

7 STEPS


1 Open your workbook. Select the cell where you’d like the formula result to appear. In, this case, I chose the bottom of the Gross Sales column.

Step 1 image

2 Click Formulas

Step 2 image

3 Click Insert Function...

Step 3 image

4 Select a function from the list. Click OK

Step 4 image

5 Select the range of cells on which you'd like to perform the calculation.

Step 5 image

6 Click OK

Step 6 image

7 That's it. You're done.

Step 7 image

Here's an interactive tutorial for the visual learners

https://www.iorad.com/player/166330/Can’t-Remember-That-Excel-Formula--Build-It

There are our top five Excel tips for 2019. We hope these tips and tricks help you get more done in less time. Do you have a favorite Excel tip or trick you’d like to share? Leave it in the comments below.

Want more Excel tips? Check out these links.

Other Posts You May Enjoy

OneDrive How to: Retrieve Important Documents with Fetch

We’ve all done it, forgot to bring an important document for that business meeting or school presentation. While this can really ruin your day, it doesn’t have to. OneDrive Fetch to the rescue. Access local files stored on one computer remotely from another. You can even stream videos or watch a slideshow of your photos.

Acumatica Pro Tip: Timecard Posting Options in Acumatica Project Accounting

One of the most common issues project focused organizations face is accurately tracking the labor and other resources they use on each project. Acumatica understands this and has developed its cloud ERP with a project accounting module that gives organizations like yours the flexibility to choose how you would like to assign expenses and resources to projects.

Do You Do A Lot of Work in Excel? Paste Special Should Be Your Best Friend

Accounting and finance pros, who work in Excel, would you like to get more done in less time? Meet Excel’s little known Swiss Army knife, the paste special feature can speed calculations, formatting and other tedious tasks. Once you discover all you can do with this tool, you’ll wonder how you got along without it for so long.

Acumatica Pro Tip: Tame Document Overload with Acumatica File Management Tools

One of the most frustrating parts about collaborating on file is often keeping track of which version is most up to date. No one likes putting hard work into a file, only to find out their team members are already three steps ahead of them. See how Acumatica’s file management features put an end to these issues.

Acumatica Pro Tip: How to Create an Acumatica Favorites List

Tired of searching through endless menu options to find the windows you use most often? Set up your Acumatica favorites list to save yourself time and energy! Just like your favorite Internet browser, Acumatica offers a tool to help you store and easily access the windows that are most important to you. Learn how to set it up.