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

Creating Basic Formulas Part 2: Multiplication

December 23, 2022 by Madeleine Moucheron

Addition, subtraction, multiplication, and division are essential mathematical functions that can be made easy by using Excel formulas. 

In Part 1 of this series, we covered creating basic addition and subtraction formulas in Excel. This week we’ll tackle how to multiply with the program.

Multiplication Formula

Like adding and subtracting, multiplying cells can be done by typing in the totals or by using the cell names. Let’s say that you want to multiply F4 and F8, which will be 12 and 3 for this example. 

You can name the cells in the formula  =F4*F8, or you can type the totals into the formula =12*3.

The PRODUCT function can also be used to multiply different cells. To use it, click on the cell where you want your total to appear, then type in “=product” and choose the PRODUCT function. Then, as with using the =SUM function, select the cells you want to multiply and hit Enter.

We at Learn Excel Now hope you feel comfortable creating addition formulas with Excel. Next week, we’ll wrap up this series with how to divide in Excel so stay tuned!

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

Creating Basic Formulas Part 1: Addition and Subtraction

December 15, 2022 by Madeleine Moucheron

Excel can be intimidating for people who have just been introduced to the program, but basic mathematical functions are relatively easy through Excel when you know the right formulas.

To begin, we’ll just cover the basic formulas for now: Addition, Subtraction, Multiplication, and Division.

Let’s begin with Addition and Subtraction.

Addition Formula

The basic way to add numbers from different cells on your Excel spreadsheet involves writing the first cell name (the column and row letter and number), then the plus (+) sign, then the next cell name. For example, =C5+C8.

If you want, you can also combine the numbers in the cells you want so that you don’t have to type in the cell names. This can be done by typing the list of numbers into the upper input bar. For example, =5+3+4+12+9 (let’s say these are the numbers in E3 to E7).

You can also use the SUM function by typing “=sum” into the area where your totals will be recorded. Once the SUM function populates, you can select all of the cells you want to include in the addition formula.

Subtraction Formula

Much like creating an addition formula, subtraction can be done by typing in the first cell name you want to use, then the subtraction (-) sign, then the next cell name. For example, =B4-B2.

You can also subtract the number totals the same way you can with addition. However, there is no subtraction function in Excel.

We at Learn Excel Now hope you feel comfortable creating addition formulas with Excel. Next week, we will cover how to multiply in Excel so stay tuned!

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

Excel Pivot Tables: Using Slicers to Filter Data

September 30, 2022 by Austin Chia

Are you looking for a way to filter your Excel pivot tables quickly? If so, then you need to learn about slicers! Slicers are a great way to control the data that is displayed in your pivot table.

In this blog post, we will introduce you to pivot table slicers and show you how to use them through a step-by-step tutorial.

So, if you want to learn how to use slicers with pivot tables, keep reading!

What Are Pivot Table Slicers?

Pivot table slicers are a new feature only found in versions from Excel 2010 onward. They allow you to quickly filter pivot table data by clicking on a value in the slicer.

Why Use Pivot Table Slicers?

Pivot table slicers are a great way to filter pivot table data in Excel. Slicers are an alternative to the default filters in Excel.

They are easy to use and they provide a quick way to change the data that is displayed in your pivot table. You’ll be able to mine data for useful business insights.

Another advantage of using slicers is that they can be used to filter multiple pivot tables at the same time. This is because slicers are connected to pivot tables.

Therefore, if you have multiple pivot tables in your workbook, you can use a slicer to filter all of them at the same time. This can save you a lot of time if you need to regularly filter pivot table data.

When Should Pivot Table Slicers Be Used?

Pivot table slicers should be used when you need to quickly filter pivot table data. They are handy if you have multiple pivot tables in your workbook.

They’re also a great way to filter data when creating an Excel dashboard for your Excel project.

With the use of pivot table slicers in Excel, you’ll get to dig deeper into your data and visualize them better through charts.

Although not as powerful as the filters available in other data analysis tools like Tableau, slicers are easy to create and implement in your work!

How Do Pivot Table Slicers Work?

Pivot table slicers work by connecting to pivot tables. When you create a slicer, you need to specify which pivot table it should be connected to. Once a slicer is connected to a pivot table, it can be used to filter the data in that pivot table.

If you have multiple pivot tables in your workbook, you can connect a slicer to all of them. This will allow you to quickly filter the data in all of the pivot tables at the same time.

How To Use Pivot Table Slicers to Filter Data

Now that you know the basics of using pivot table slicers, let’s take a look at how to use them with a pivot table. We will walk you through the process step-by-step so that you can see how it’s done.

To start, select the pivot table with which you want to use the slicer. Then, click on the “Insert” tab and then click on “Slicer.”

In the “Select a slicer” window, select the field that you want to use as a slicer. For this example, we will use the “State” field.

Next, click “OK.” Your pivot table should now have a slicer associated with it. To use the slicer, simply click on the items that you want to include in your pivot table.

For example, if you only want to see data for the states of “CA” only, click on “CA”. The pivot table will update to only include data for that state.

And if you want to filter and include bot, hold Shift and click both “CA” and “NY”. The pivot table will update to only include data for those two states.

By using this slicer, you’re able to quickly switch between states by selecting the values you need. This can be great when presenting important data using your pivot table.

Rather than just using the regular filters in the pivot table slicers give you a more intuitive way to interact with your data!

Having this knowledge of using slicers in your pivot tables is an essential skill in data analytics in business, where presentation summaries are used in day-to-day operations.

Key Takeaways:

  • Pivot table slicers are a great way to quickly filter pivot table data.
  • They’re easy to use and can be connected to multiple pivot tables.
  • You can use them to filter data by region, sales, criteria, etc.

Conclusion

As you can see, pivot table slicers are a great way to quickly filter your data. So, if you haven’t already started using them, we encourage you to do so!

Thanks for reading!

Enjoyed this basic tutorial on separating data? Having basic training in Microsoft Excel is important for success in many jobs. If you want to learn more about how to use Excel, check out the other blog posts or sign up for one of our Excel trainings!

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

Author Bio

Austin Chia is the Founder of Any Instructor. A data analytics and Excel enthusiast, he seeks to help others learn more about Excel and anything related to analytics and tech. He has experience as a data analyst and data scientist in healthcare and research.

Excel UNIQUE Function: Explained in 4 Minutes

September 29, 2022 by Tyrone Pernsley

The Excel UNIQUE function returns a list of unique values in a list or range.

Note: This function is currently available only to Microsoft 365 subscribers.

To follow along, and for future reference or practice, the file Mike is working from can be found here. 

The formula in this video: =UNIQUE(B2:B19)

The tutorial in this video is included in the following interactive workshop: Excel Formulas & Functions 101

Thanks for watching!  We’re here to take the fear out of Excel, by empowering you with the essential tools and knowledge necessary for mastering it!

Become a member today: Learn Excel Now All-Access: One-Year Membership

Connect with us on social:
LinkedIn Facebook Twitter

Excel Date Calculations Part 2: Setting Sequential Dates

December 21, 2016 by Tyrone Pernsley

Excel offers a variety of ways to perform date calculations. In part 1 of this series, we showed you how to find the difference between dates. In this week’s post, we cover setting sequential dates in Excel.

Sheryl is getting ready to perform her annual budget. Her job pays her every two weeks. As a result, 10 months of the year, she receives two paychecks, but there are two months where she ends up with three paychecks. She wants to find each payday in the upcoming year and highlight the months where she will receive three checks.

She starts off with her current payday date:

excel-date-calculations-2

To find the two week pay schedule dates she will:

  • Enter the formula =A2+14
  • This yields the next pay day
  • Then copy the formula across to find all dates
  • Then look for the date with three in a month and highlight them

excel-date-calculations-2

In the example, we see that Excel automatically finds the date 14 days from the original date input. In copying the formula across it populates each date two weeks apart.

We at Learn Excel Now hope you now feel confident setting sequential dates in Excel. This process can be used for a variety of business and personal situations.

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

Excel Rank Function: What is It & How to Use It

November 30, 2016 by Tyrone Pernsley

One of Excel’s advanced functions is the RANK function. This formula is used to rank numbers in a dataset by either ascending or descending order.

rank-function-featured-image

Let’s say you were running a customer loyalty program based on earning points. You need to rank customers’ point totals to determine what rewards they receive. You could do a simple sort, but your boss wants the customer names to stay in the same order.

This is the example we will use:

rank-functino-image-1

You will use the RANK function for this challenge, but let’s first explore how the function works.

Syntax of the Rank Function

=RANK(Number, Ref,[Order])

When you use the rank function, your first argument is the number. This is where you specify the cell containing the number you want to be ranked. Next, you identify the range of numbers to be used as the reference (Ref). Finally, you select the order, which is ascending or descending.

Solving the Challenge

So, we will identify B2 as the cell to be used. Then, identify the range as B2 through B11:

rank-function-image-2

Then, select Ascending order and we get our first result:

rank-function-image-3

Before moving onto the remaining lines in the table, we need to add absolute referencing to the range so that it stays consistent while the number being compared changes for each line. A reminder to use the $ between the letter and number of the cell for absolute referencing:

rank-function-image-4

Once you’ve added the absolute reference, you can now copy and paste the formula all the way down to reveal the rank for all lines:

rank-function-image-5

We at Learn Excel Now hope you feel comfortable using the RANK function after this article.

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

How to Use the Excel MEDIAN Function

November 9, 2016 by Tyrone Pernsley

In this week’s Excel tip, we cover how to use the Excel MEDIAN function. The MEDIAN function is used the return the median value within the defined range.

For this demonstration, we will use the follow example:

median-image-1

The task assigned for this spreadsheet was to find the median sale amount. To do this, we will use the MEDIAN formula:

=MEDIAN(Number1, [number2]..)

Since the goal is to find the median in a single range, the formula becomes:

=MEDIAN(D2:D21)

median-image-2

And once you enter the formula, you get the return value:

median-image-3

Since there were 20 cells within the range, the formula found the average between two middle values.

We at Learn Excel Now hope you feel comfortable using the MEDIAN function now.

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

Excel Formulas: How to Use the LEFT and RIGHT Functions

November 2, 2016 by Tyrone Pernsley

This demonstration covers how to use the LEFT and Right Functions in Excel. These are text functions. In the LEFT function, you can pull a set number of characters out of a cell into another cell starting at the leftmost point. The RIGHT function performs the same except starting at the rightmost point.

left-right-functions-featured-image

Both functions are related to the MID function, which we covered back in September. At the time, one of our customers asked for a demo on the MID function. Since then, we’ve had several follow up requests on the LEFT and RIGHT.

For today’s demonstration, we will be using the following spreadsheet:

left-right-functions-image-1

In this example, Column A contains a 6-digit number hyphenated with a 4-digit number. The 6-digit number is the order number and the 4 digit is the client ID. Your job is to separate the order number and client ID into Columns C and E, respectively.

How is this done?

For the order number, we will use the LEFT function. We know the code is 6 digits, so we have the number of characters. So in cell C2, you will enter the following formula:

=LEFT(A2,6)

For the client ID, we will use the RIGHT function. In cell E2, you will enter the following formula:

=RIGHT(A2,4)

left-right-functions-demo-gif

LEFT Function Syntax:

=LEFT(Destination Cell, Number of Characters)

This tells Excel: Starting on the left of this specified cell, copy to this many characters.

RIGHT Function Syntax:

=RIGHT(Destination Cell, Number of Characters)

This tells Excel: Starting on the right of the specified cell, copy to this many characters.

As you can see in the above demonstration, once the functions have been entered, they can be copied all the way down to finish filling in the data.

We at Learn Excel Now hope you feel comfortable using the RIGHT and LEFT functions.

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

How to Show Formulas in Excel

October 26, 2016 by Tyrone Pernsley

In this week’s blog post, we cover how to show formulas in Excel. This convenient feature is ideal for identifying which cells contain formulas and what those formulas are.

show-formulas-featured-image

John was recently hired in human resources for a large sales firm. His job is to manage the salaries for the sales, accounting and shipping departments. Once hired, he received the following spreadsheet from his predecessor.

show-formulas-image-1

He was not a fan of the layout and functionality of the spreadsheet. He wanted to change it around to make more sense to him and to give him more control over the data. However, he knew some of the cells contained formulas and moving things around could throw off the whole spreadsheet.

So, his first step in taking ownership over the spreadsheet was to identify which cells are using formulas. There are two ways to show formulas in Excel:

  • On the Excel Ribbon, go to the Formulas tab and click on the Show Formulas button
  • Keyboard shortcut: Ctrl+` (Control and the accent mark, aka Tilde)

The following Gif provides a demo using the first option:

show-formulas-gif

However, using the Ctrl+` would result in the same.

show-formulas-image-2

We at Learn Excel Now hope you now feel confident finding the formulas in your spreadsheet.

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

Excel Formulas: Using Trace Dependents to Find Data Solutions

October 19, 2016 by Tyrone Pernsley

In last week’s post, we covered how to use Trace Precedents to find and resolve formula errors. In this week’s follow up, we cover how to use trace dependents to see which formulas use the data contained in a particular cell.

Donna was recently sent her company’s sales spreadsheet:
trace-dependents-image-1

The commission rate was set to 5% for sales over $500. Her boss wants to increase the rate to 6%. She has two tasks: identify which sales will be affected by the increase and how many sales are over $500.

There are a number of ways Donna can find the data she needs. She could manually count the sales columns. She could use a lookup function. However, she prefers to have a clear visual to show which ones are the important ones.

By using Trace Dependents, she can quickly see which bonuses are for sales over $500, allowing her to determine which ones will be impacted and how many there are. Here is how she used Trace Dependents:
trace-dependents-gif-1

  • Find the cell with the relevant data – in this case, the 5% in the commission table (Cell I6)
  • While you are on that cell, click the Formulas tab in the Ribbon
  • Under the Formula Auditing menu, click on Trace Dependents
  • The arrows will point to every cell using a formula which uses I6

We at Learn Excel Now hope you feel confident using Trace Dependents now. There are many times and reasons this tool will come in handy.

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

  • 1
  • 2
  • 3
  • Next Page »
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