Award Winning San Francisco IT Services

Tech Elite
Google Rating
5.0
Based on 40 reviews

Excel Like A Pro Part III

This is the final of a three-part series about using Microsoft Excel 2016. It will cover some of the more advanced topics. If you aren’t great with numbers, don’t worry. Excel does the work for you. With the 2016 version of Excel, Microsoft really upped its game. Excel’s easy one-click access can be customized to provide the functionality you need.

If you haven’t read Part I and Part II of this series, it’s suggested that you do so. The webinar versions can also be found on our site or on YouTube.

This session will discuss the following:

More with Functions and Formulas

  • Naming Cells and Cell Ranges
  • Statistical Functions
  • Lookup and Reference Functions
  • Text Functions

Documenting and Auditing

  • Commenting
  • Auditing Features
  • Protection

Using Templates

  • Built-In Templates
  • Creating and Managing Templates

More With Functions And Formulas

Naming Cells And Cell Ranges

How do you name a cell? You do so by the cell’s coordinates, such as A2 or B3, etc. When you write formulas using Excel’s coordinates and ranges you are “speaking” Excel’s language. However, this can be cumbersome. For example, here G12 is significant because it refers to our Team Sales.

You can teach Excel to speak your language by naming the G12 cell Team Sales. This will have more meaning to you and your teammates. The benefits of naming cells in this fashion are that they are easier to remember, reduce the likelihood of errors, and use absolute references (by default).

To name our G12 cell Team Sales, right-click on the cell, choose Define Name, and type “Team Sales” into the dialog box. You can also add any comments you want here. Then click Ok.

 

Another way to do this is to click on the G12 cell and go up to the Name Box next to the Formula Bar, then type your name there.

And, there’s a third option at the top of the page called “Define Cells” that you can use.

Excel Like A Pro

Notice that there’s an underscore between Team and Sales (Team_Sales). There are some rules around naming cells:

  • You’re capped at 255 characters.
  • The names must start with a letter, underscore or a backslash ().
  • You can only use letters, numbers, underscores or periods.
  • Strings that are the same as a cell reference, for example B1, or have any of the following single letters (C,c,R,r) cannot be used as names.

How To Name A Range

Highlight an entire range of cells and name your range (we’re doing this in the upper left-hand corner).

 

Excel Like A Pro

Then you can easily use the name to produce the sum you need:

Excel Like A Pro

You won’t have to go back and forth from spreadsheet to spreadsheet clicking on specific cells to calculate your formula. You simply key in the name of the cell range you want to add. Just be sure to remember the names as you build your spreadsheets over time.

If you ever make a mistake or want to change names, you can go to Name Manager to do this.

Remember that if you move the cells, the name goes with it.

Statistical Functions

The three statistical functions are:

  1. Average If
  2. Count If
  3. Sum If

The Average If can be used to figure out the average of a range based on certain criteria. Here we’re going calculate the Average If of the ERA of 20+ Game Winners from the spreadsheet we developed in our last session.

We’ve already named some of our cell ranges (wins, era). And we want to know the average greater than 19.

Excel Like A Pro

Hit Enter and you have the average.

You can use this feature across a wide variety of scenarios. For example, if you wanted to know the average sales of orders above a certain quantity – or units sold by a particular region, or the average profit by a distinct quarter.

Count If is used for finding answers to questions like, “How many orders did client x place?” “How many sales reps had sales of $1,000 or more this week?” or “How many times have the pitchers of the Philadelphia Phillies won the Cy Young Award?”

As you can imagine, it’s essential that you type in the text exactly the way you named that particular cell.

Hit Enter and you get your answer