Learn Excel Now

Conquer the Fear of Excel

  • Home
  • Training
    • Excel
    • MS Office
    • Outlook
    • PowerPoint
    • Word
    • OneDrive
    • Google
  • All-Access
  • eLearning
    • Excel Foundations
    • Micro Courses
      • Simply Excel
  • Resources
  • Login

How to Calculate a Subtotal In Excel Using the Filter

July 20, 2016 by Tyrone Pernsley

Excel has many ways to calculate data. If you know the right formulas and functions, you can find out just about anything you want to know about your data. Today’s lesson is on using the subtotal formula to find various totals based on the filter option in Excel.

Imagine you are running sales numbers using the following the spreadsheet:

Calculating Subtotals Image 1

If you remember from last week’s blog post on Sorting and Filtering data, we have gone ahead and added the filter. Now, if you wanted to find the sales total, you can use the following formula:

=sum(range)

This is the standard way to find a total. But, as you can see, once you use this formula and change the anything from the filtered drop-down menu, the sales total doesn’t change with it:

Calculating Subtotals Gif 1

So, how do you get the total to change with changes you make on the filter? This is where you will use the Subtotal formula. The Subtotal formula is:

=subtotal(function_number, ref1…)

Note: You need to select which function you want the subtotal to use. For finding sums, we use function 9-Sum. As you can see from the following menu, there are multiple functions to choose from:

Subtotal Menu Options

Once the function is selected it’s time to enter the range. For the spreadsheet example, we are looking for subtotals on the Sales column, Column D. So, the final formula looks like this:

=subtotal(9,D2:D21)

When you first enter this formula in cell D22, it gives you the total amount, the same as when you enter the Sum formula. However, watch what happens when you change the options using the Filter drop-down:

How to Calculate a Subtotal In Excel Using the Filter

As you can see, the Subtotal formula lets you find totals by Salesperson, Client, Product, etc. quickly and easily. And the total changes with the information you select.

We here at Learn Excel Now hope you found today’s tip on how to calculate a subtotal useful.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Hyperlinking In Excel: To the Web or to Another Worksheet

May 18, 2016 by Tyrone Pernsley

You might already know how to hyperlink a cell to external sources, namely to the web. In case you don’t, here is a quick tutorial on that:

  • Copy the URL of the website you want to hyperlink to
  • Click on the cell you want to hyperlink from
  • Right click and open the option that says “Hyperlink”
  • By default, Excel sets the dialogue box for hyperlinking to external web content
  • Paste the URL of the site you’re hyperlinking to (you can also type it if you have that kind of time)
  • Then click Okay and you are cell is hyperlinked!

Hyperlinking in Excel Gif 1

But did you also know you can hyperlink to other sources – such as within the worksheet itself?

This is convenient for large workbooks where data on one worksheet references data on another worksheet. One of my favorite things to do with large workbooks is to create a Table of Contents that identifies what each worksheet contains. Then, using the hyperlink option, anyone using the workbook can click right to each worksheet.

To do this:

  • First select a cell in one sheet that you want to create a hyperlink to another sheet
  • Right click the cell and click the hyperlink button
  • From the menu that appears, click the Place in This Document button in the Link to box
  • Select the sheet that you want to hyperlink to and enter the text you want to display in the Text to display box (or keep the text the same if you want)
  • If you want to hyperlink to a specific cell in another sheet, specify the cell in the Type the cell reference box.
  • Click OK and you will see that when you click on the data it will bring you to your desired worksheet!

Hyperlinking in Excel Gif 2

We hope you found today’s quick and easy Excel lesson beneficial. Don’t forget to follow ups on Social Media and subscribe to the blog to get convenient, quick tips like this, and other great Excel training tips so that you can take the fear out of Excel.

Like Learn Excel Now? Sign up for the newsletter!

-Kevin, Learn Excel Now

Making Sense of VLookUp: Quick and Easy Tips

April 27, 2016 by Tyrone Pernsley

Making Sense of VLookUp 

At Learn Excel now, we are always getting questions about VLookUp. Making sense of VLookUp requires breaking the function down and understanding the key components and what you are trying to find.

What is VLookup?

The VLookup function allows you to find data that is stored in a table from another spreadsheet or a smaller table.

There are 2 ways to enter the VLookUp Function:

  1. Go to the Formulas Tab, click the “Lookup & Reference” dropdown and select Vlookup. This will open the following dialogue box:Making Sense of VLookUp Image 1
  2. You can enter the function as a formula:
    =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup = TRUE])

Syntax of the VLookup Function:

Lookup_value: The value to search in the first column of the table. This can be a reference or a value.
Table_array: This is two or more columns of data. You can use a reference to a range or a range name. The values that appear in the first column are those searched by the lookup_value. These values can take many forms including text, numbers, or logical values.
Col_index_num: This is the table from which the matching value is returned. There are several different scenarios for the value’s placement:

  • If the col_index_num is less than 1, then the vlookup shows the error “#Value!”
  • If the col_index_num is greater than the number of columns in the table_array, then the vlookup shows the error “#REF!”
  • If the col_index_num is 1, then the value will be in the first column of the table_array.
  • If the col_index_num is 2, then the value will be in the second column of the table_array (and so on)

Range_lookup: This function is meant to specify whether vlookup should find an exact or approximate match of the value you search.

  • If “true” or 1 is used, then an exact is returned unless there is not an exact match, in which case the next largest value that is less than the lookup_value is returned.
  • If “false” or 0 is used, then an exact match is returned. If there are two or more values that are an exact match, the first value found is used. If no exact match is found then the error “#N/A” is returned

S0, what does that all mean?

Let’s say you wanted to find the total profits from sales to XYZ Company in the following table:

Making Sense of VLookUp Image 0

Now, in this simple table, you could just sum the individual cells under the Profit heading that correspond to XZY Company. But, if this table were hundreds of rows long, that would be impractical.

So, we are going to use VLookUp:

=VLOOKUP(“XYZ Company”,A1:E19,5,)

Making Sense of VLookUp Image 2

In this formula:

XYZ Company Acts as the Lookup_value
A1:E19 is the table_array
5 is the col_index_num
Because we are just looking to return a value, there is no Range_lookup required.

Other Helpful Tips:

  • You need to use “Ctrl,” “Shift,” and “Enter” to create brackets around an array formula rather than simply using “Enter.”
  • Keep in mind that table_array doesn’t differentiate between uppercase and lowercase letters.
  • Put the value of the first column of the table_array in ascending sort order to ensure that vlookup gives you the correct value. To set this, click on the “Data” icon on the tool bar, then select “Sort,” and finally select “Ascending.”
  • If you get the error message “#N/A”, check to make sure that the lookup_value is not smaller than the smallest value in the first column of the table_array.

We here at Learn Excel Now hope you now feel more comfortable using VLookUp. It is one of the most widely used Excel functions but it is a complicated tool. Making sense of VlookUp requires practice and use.

Like Learn Excel Now? Follow our social media pages and share our Excel content with your networks!

Kevin – Learn Excel Now

 

Excel Essentials: How to Remove Duplicates in Excel – Video

April 6, 2016 by Tyrone Pernsley

How to Remove Duplicates in Excel

Clean, readable data makes Excel much easier to use. A common struggle is how to remove duplicates in Excel. Having too many duplicates causes inaccuracies in reporting and analyzing data. But scrolling through your data to hunt and peck duplicates is tedious and time-consuming – especially when you have hundreds of rows of data.

Even using the Find function (keyboard shortcut: Ctrl+F) can take forever. But Excel provides built-in tools to help you with this.

Please enjoy the following video on how to remove duplicates in Excel:

We here at Learn Excel Now hope you know feel confident in your ability to remove duplicates in Excel

To discover essential tips like this and so much more, please join us for:

Excel Essentials: Most Needed Skills in Today’s Workplace
Wednesday, April 13, 2016

In this live, online event, our Excel expert instructor will cover the key Excel skills you need for workplace success.

Like Learn Excel Now? Sign up for our weekly tips, announcements and offers!

Getting social with Excel – don’t forget to follow us on social media and share our content with your networks!

Kevin – Learn Excel Now

All Access Subscription
Our 1-year all-access subscription provides step-by-step guidance for mastering Microsoft Office applications, with workshops, exercises and quick reference eGuides. It's perfect for anyone that needs to brush up on certain skills or even folks who need to learn the programs - from a beginner’s level.
Excel Foundations
Take the fear out of Excel with this 20 module self-paced training course. You will cover the most essential topics to develop a solid foundation of Excel or a lifetime of mastery.

Learn Excel Now helps you conquer the fear of Excel. By providing self-paced and instructor-led training and free strategies and guides, we leave no Excel mystery unsolved.

  • Home
  • Training
  • All-Access
  • Excel Foundations
  • Resources

Connect with Us

  • Facebook
  • Linkedin

Contact

Learn Excel Now
questions@learnexcelnow.com
1-484-259-7664 or 1-800-964-6033
660 American Ave
Suite 203
King of Prussia, PA 19406

Microsoft® Office Excel® is a registered trademark of the Microsoft Corporation in the United States and other countries. All rights reserved.

Copyright © 2023 ­Learn Excel Now

Terms of Service

Privacy Policy

Copyright © 2023 · News Pro Theme on Genesis Framework · WordPress · Log in

WELCOME BACK!

Enter your username and password below to log in

Forget Your Username or Password?

Reset Password

Lost your password? Please enter your username or email address. You will receive a link to create a new password via email.

Log In