How to Master Microsoft Office Excel

Posted on March 30, 2016 by resourcefulman

Spreadsheets are a data-driven nerd’s dream. For most ordinary people, however, they are a complex mess. Fortunately, this is not necessary. Here’s how to bring data to your will with Microsoft Excel 2016.

Excel Quick Start

Getting started with Excel is deceivingly easy. The Ribbon interface works exactly like other Microsoft applications . Each tab at the top opens up a new set of menu options. If you cannot find the function you are looking for, explore the other tabs to find it. You can also click the green box that says “Tell me what you want to do” to find a menu item.

When creating a new document, you can also choose from a variety of templates. Excel 2016 can search thousands of online templates, including personal budgets, vacation itineraries, expense reports, inventory lists, and more. You can search the list of templates for keywords or view tag pages right in the app. You can also view online Excel templates here .

It assumes you are familiar with the basics, but if you need a reminder, you can check out the official Microsoft quickstart guides here .

How to perform the most common important tasks in Microsoft Excel

Tables can be as simple as a base table and as complex as an automated RPG character sheet . While everyone’s needs are different, there are a few basic tasks that you can combine to create the most robust spreadsheet you need. Here’s how to accomplish the most important tasks.

How to apply conditional formatting

Formatting cells in a spreadsheet can turn a boring list of numbers into a useful document that is easy to read. You can manually set the text and background colors, text size, emphasis and borders to make it easier to read. The most useful feature by far, however, is the ability to style cells based on what’s in them.

For example, in the screenshot above, I have a spreadsheet with comic book movies and how much money they made at the local and global box office (yes, I do have that, shut up). If I want to see at a glance which films have done the most and which ones have done the least, I can use conditional color scales.

To apply a style, select the cells to which you want to apply the style. In my case, I selected all cells in one column. On the Home tab, you will find a Conditional Formatting button. Click here and you will see a selection of styles that you can apply. As you hover over each menu item, you will see a live preview to see how your data will look after you make your selection. In the screenshot above, I have used a style called Green-White-Red Scale. The largest numbers are colored green and the smallest numbers are colored red.

If you decide you don’t like the style or want to change it, you need to clean up the old style first. To do this, follow these steps:

Click the Home tab.
In the “Editing” section, click the “Clear” button.
From the drop-down menu that appears, select Clear Formats.

You can apply any number of styles to a range of cells. You can also create your own styling rules if Excel doesn’t have a built-in option you want. Keep in mind that the style will be applied to every cell you select, so how you select the cells can affect how the style looks. For example, in the table above, if I applied the style above to the domestic and global box office columns at the same time, all the interior values would be at the red end of the spectrum, rendering the style useless. To be useful, I needed to style each column individually.

Freeze rows and columns for easier viewing

The first row or column of your spreadsheet is often used for shortcuts. In my sample spreadsheet, I want to see which column I am working in, no matter how many superhero movies I need to scroll through. You can lock your shortcuts in place with the docking panels feature .

On the View tab in the Window section, you will see a Dock Panels button. If you click the dropdown menu here, you will find three options:

Freeze Panels: This option will lock any rows above the cursor and any columns to the left of the cursor. So, for example, if you want the first row and first column of your spreadsheet to remain visible, place your cursor in cell B2 and click this button.
Freeze Top Row: This option will freeze only the first top row in the spreadsheet.
Freeze First Column: This option will freeze the leftmost cell in the spreadsheet.

After you have selected any of these options, the first Freeze Panes option will change to Unfreeze Panes. You will need to unfreeze all panels before selecting new ones to lock.

Use formulas and functions for complex math calculations

All these boxes of numbers won’t be very useful if you can’t do anything about them. Excel formulas and functions can help you perform a ton of complex calculations so you can draw meaningful conclusions from your data.

Formulas are the easiest way to perform mathematical calculations in Excel. All formulas begin with an = sign. Then you can create basic problems using cell labels. For example, in the screenshot above, I want to see how many two separate Wolverine films have been made in the world. I would use the following formula:

=F29+F31

Adding the two numbers – $ 373 + $ 414 – gives me a total global total of $ 787 million (the value of each cell represents millions).

Excel also has many out-of-the-box functions for performing more complex mathematical calculations. For example, suppose you want to know how much money the X-Men movies have made on average worldwide, in the table above. To do this, you can use the AVERAGE function . To take the average for all of these movies, you should use the following line:

=AVERAGE(F26:F33)

A colon in this row indicates that the AVERAGE function should include the entire range of cells between the first and last cell. So this function will take the average of each value for the X-Men movie in column F.

You can also nest functions within each other. For example, let’s say I want to know the average Rotten Tomatoes user rating for each X-Men movie. The average rating of all cells from H26 to H33 in the screenshot above ends with three decimal places. Instead, I want to round this number to one decimal place. According to the Excel documentation, the ROUND function should have the following format:

=ROUND(number, num_digits)

Here “number” indicates the number I want to round, and “num_digits” represents the number of digits I want to round the number to. In this case, the number I want to round is the result of the AVERAGE function. So, to cut off those extra decimal points from the mean, I’ll use the following function:

=ROUND( (AVERAGE(H26:H33)) ,1)

This is what is called a nested function . Excel first calculates the average of cells H26 through H33 (shown in bold above). It then uses this average as a numeric argument to the ROUND function. The 1 at the end of the line means that the number should be rounded to one decimal place. So instead of getting an average grade of 78.875, which is the actual average grade, you see 78.9 in the final table.

As you can see, formulas and functions can range from very simple to incredibly complex. These examples are just a small part of what you can do with formulas and functions. You can check out this tutorial from How-To Geek to dive deeper into what you can do with the features. You can also view functions built into Excel and learn how to use them here .

Use pivot tables to draw meaningful conclusions

In the example above, I used functions to manually create a table with information about my spreadsheet. Pivot tables offer an easier way to do this without having to carefully create complex formulas for basic tasks such as rounding the average. When dealing with large datasets, this can be invaluable.

You can create a pivot table in one of two ways. On the Insert tab, you can click Recommended Pivot Tables and Excel will suggest tables based on the data you’ve already entered into your spreadsheet. Alternatively, you can click the pivot table to manually create your own. Both will open the pivot table interface in a new sheet.

In the pivot table, you can select specific subsets of the data to analyze. For example, let’s say I want to see the average scores in Metacritic reviews. On the right side of the screen, I’ll select the Film, Metacritic, and Metacritic Users fields. This will give me the following table:

By default, the table displays the sum of each row. While that would be handy for something like general box office receipts, the sum of the review scores is useless in this case. I need to fix it. To do this, I’ll click the drop-down arrow next to Sum of Metacritic in the Values field. Then I click on Value Field Options. This will give me new options for displaying data.

On the Summarize Values By tab, I’ll choose Average, as this is a more meaningful calculation. Then I will do the same for the Sum of Metacritic Users field in the Values field. This will give me a much more useful table:

As in the previous section, I’m worried about the final decimal in the Metacritic column. Fortunately, this time I don’t have to deal with nested functions. To fix this, I’ll open the Value Field Settings menu again. This time I’ll click the button at the bottom of the window that says Number Format.

In the list of categories on the left side of the new window that opens, I will select Number. Here I can choose how many decimal places I want to include. For the Metacritic column, I’ll choose zero to match the original score metric. I’ll make the same change for the Metacritic Users column, but this time I’ll choose one decimal place as Metacritic users rate it on a scale with one decimal point. My new table is much cleaner:

My new pivot table is much easier to read. You can use pivot tables to explore your data in a variety of ways without changing the worksheet where your data is stored. For example, I can quickly add columns for box office data or ratings on Rotten Tomatoes. I could also drag the Movie field from the Rows field to the Filters field so that my table only shows the totals for each column, not individual rows.

When you are dealing with huge datasets, pivot tables become indispensable. They can sometimes be a little tricky if your data isn’t exactly formatted correctly (the blank lines I leave between different franchises, like automatic pivot tables dumping on a regular basis), but they are often much faster and more flexible than creating your own tables from formulas.

Insert page breaks to make it easier to print spreadsheets

Unlike Word documents, spreadsheets are not designed for normal, printable pages. To compensate for this, use the page break preview feature . With it, you can change how the sheet is divided and what data should be printed on which page.

To open this view, click the View tab. In the Book View section, click Page Break Preview. Your table will zoom out and show you which sections will be printed on each page. You can drag each row to resize the sections as you see fit.

If you want to add more page breaks, follow these steps:

Place your cursor in the row or column where you want to create a new page break.
Click the Page Layout tab in the ribbon interface.
Click the Breaks button.
In the pop-up dropdown, click Insert Page Break.

For very long documents, Excel may introduce automatic page breaks when it reaches the maximum amount of space that can fit on a page. You cannot remove these page breaks, but any others, including the page breaks you added, can be moved or removed at will. To return to normal view, return to the View tab on the Ribbon. In the Book Views section, click Normal.

Create charts to visualize your data

To better understand what your data looks like, you can create your own charts based on all or part of the data in your spreadsheet. Excel comes with many predefined styles that you can choose from, or you can customize your charts to suit your needs .

To create a chart, first select the cells you want to include. In some cases, you may need to format cells in a certain way for Excel to understand them, but you can read about how to format cells for each chart type here . For this example, we’ll use the Marvel Cinematic Universe movie data shown in the screenshot above. I will select everything in columns A, E and F.

With your data selected, click the Insert tab on the ribbon. In the “Charts” section, you will see the “Recommended Charts” button. This will automatically select the type of chart that Excel thinks is best based on the data you select. In my case, I chose “clustered columns” from my suggestions.

Once you’ve created your chart, you can customize it the way you want. In my case, there are a few things I don’t like about this. I want to change the title of the chart to be more informative. I want to add a label to the left axis to indicate that numbers are measured in millions. It would also be nice if the color scheme was a little less boring.

The first problem is simple. You can click the title of the chart to change it. To change the axis labels, you need to add one first. Click anywhere on an empty spot in the diagram to select the diagram itself. Three buttons appear near the top-right corner of the chart. Click the first button with a green plus sign. Here you can add items such as the axis title. Then you can click the label to edit it the same way you edited the title.

To change the style of a chart, select it and click the box with the brush symbol. Here you can choose one of the predefined styles. You can also change the color scheme of the chart by clicking Color at the top of the Style Picker. Now, doesn’t this diagram look much better?

By default, any new charts you create appear on the sheet you open. If you prefer to keep charts separate, you can move them to a separate sheet. To do this, right-click the chart and select Move Chart. In the window that appears, select “New Sheet” and give it a name. Click OK and the chart will move to a separate sheet.

The best new features in Excel 2016

The basics of spreadsheets are as fundamental as mathematics itself. Regardless, Microsoft has managed to add useful new features to Excel 2016. Here are some of the best tools in the latest version:

You can search in the ribbon: As with other new Microsoft Office applications, you can search in the ribbon if you can’t find the feature you’re looking for. Click the green box at the top of the screen that says “Tell me what you want to do.” Enter keywords or describe what you want to do and Excel will suggest functions for you.
Collaborate with others in real time: Another feature common to all Office apps, you can share your documents with your OneDrive account and work with others on the same document at the same time. As with Google Docs, everyone can see each other’s changes as they happen.
In-app exploration with smart search: If you need to quickly find information about a cell in a document, you can use the smart search tool to find it without leaving the app. You can get Wikipedia links, word definitions, and Bing image search results in Excel.
Draw formulas using handwritten equations: Some math equations are easier to write than to type. In Excel 2016, you can draw math equations to add to your spreadsheet. This is especially useful if your computer has a stylus or touchscreen.

There are many more old and new features buried beneath the surface. You can find out more about the latest versions on the Microsoft website .

Work faster in Excel with these keyboard shortcuts

You can find a complete list of keyboard shortcuts for almost everything in Excel on the Microsoft website here . Here are some of the more common ones that you will use every day:

Ctrl + N / Ctrl + O / Ctrl + S: Create, open and save a document.
Ctrl + X / Ctrl + C / Ctrl + V: cut, copy, paste
Ctrl + B / Ctrl + I: Bold, Italic
Ctrl + Page Up / Page Down: next / previous sheet
Ctrl + F1: Expand or Collapse the Ribbon
Alt + H: Go to the Home tab on the Ribbon.
Ctrl + Arrows: Move to the edge of a section
Shift + Space: select the entire line
Ctrl + Space: select the entire column
Ctrl +;: Enter the current date.
Ctrl + Alt +;: Enter the current time.

Excel also supports many of the same text navigation shortcuts that we covered here earlier . You can also navigate the ribbon by pressing Alt. The app will then show you which button you can click to change tabs or access currently available features.