Learn Excel Now

Conquer the Fear of Excel

  • Home
    • Our Team
  • Training
    • Custom Training
      • Custom Virtual Group Training
      • Custom Onsite Group Training
      • Consulting Services
    • Excel
    • MS Office
    • Outlook
    • PowerPoint
    • Word
    • OneDrive
    • Google
  • All-Access
  • eLearning
    • Excel Foundations
    • Micro Courses
      • Simply Excel
      • Simply Excel Pt. II
  • Resources
    • Contact
    • FAQ
    • New User Onboarding
  • Login

10 Reasons to Use Excel Tables

March 10, 2023 by Madeleine Moucheron

Excel tables come with a variety of features that make data recording and management a breeze. Instead of handling your data processes manually, save time and take steps to ensure accuracy by using Excel to your advantage.

Read on to learn about 10 beneficial features that make Excel worth a try.

Excel is designed to simplify data collection and analysis, and in doing so, this tool saves time, ensures accuracy, and provides valuable data insights. Take your tables to the next level by learning how to harness each of the features included in Excel.

10 Reasons to Use Excel Tables

1. Instant Formatting: Formatting an Excel table is incredibly easy if you use the “Format as Table” option or scroll through the various “Design” tabs. Simply select the format option you want and apply it to your entire table, or a portion of it (if desired).

2. Instant Filter Buttons: Using a feature known as “Slicers,” you can create quick, easy filters that allow you to organize your various datasets.

3. Instant Freeze: By selecting a cell below the rows you want to freeze, or to the right of columns you want to freeze, selecting the “Freeze Panes” option from the View tab instantly freezes your selection.

4. Instant Defined Area: Under the Formulas tab, the “Create from Selection” tab enables you to define specific areas within your table.

5. Easy Management of Data Ranges: If you select a specific design or format for your entire table (or sections of it), the selections remain as you go. This way, you don’t have to manually change every cell, row, or column.

6. Easy Insert/Delete Table Rows and Columns: You can right-click on any cell in your table to delete it, or you can choose to insert columns and rows above, below, or beside the cell.

7. Total Row On: Under the Tools tab and the Design subtab, clicking Total Row will insert a Total Row at the bottom of your table. Choose the column you want a total for, then choose the formula you need. The total will populate in the new row.

8. Auto Names and Fills: Clicking the Auto Fill Options tab presents you with a selection of auto-fills you can use. From there, select the cells you want to use to begin automatically filling data. If you’re numbering the cells, place 1 and 2 in the first two cells, then drag the fill handle to select the cells you want to fill. Apply the fill option you want.

9. Summarize with PivotTable: PivotTable is a useful feature that summarizes large datasets and provides viewers with analysis and valuable insights they can use to simplify data reviews.

10. Easy Convert to Range: Converting a list is easy with the “Convert to Range” option, which can be accessed through the Tools tab, then the Design subtab. 

Excel is designed to simplify data collection and analysis, and in doing so, this tool saves time, ensures accuracy, and provides valuable data insights. Take your tables to the next level by learning how to harness each of the features included in Excel.

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.

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 Basics: How To Separate Text Into Columns

August 22, 2022 by Austin Chia

Do you have a list of data that you need to separate into columns in Excel? Maybe you have an address list with the city, state, and zip code all in one column. Or maybe you have a list of product SKUs and want to separate them into individual columns.

Well, before you can start applying all your Excel formulas, you’ll need to get your data all cleaned up. Data is typically cleaned by data engineers but if you’re working with financial data that mostly live within Excel databases, you’ll have to clean it yourself.

In this blog post, I will show you how to use the text to columns wizard in Excel to easily separate text into columns.

I will explain how to use the wizard by both delimiter and fixed width. So whether your data is separated by commas, tabs, or spaces, I will show you how to get it into neat and tidy columns!

Let’s get started!

How Do You Separate Text Into Columns in Excel?

Have you ever tried to filter your data in Excel only to find that all of your data ended up in one column? Before you try out these database tips in Excel, you might want to separate your data into columns first!

It can be frustrating, but luckily there is a tool that can help. The text to columns wizard in Excel allows you to easily separate text into multiple columns.

There are two main ways to use the text to columns wizard: by a delimiter and by fixed width.

If your data is separated by a specific character, such as a comma or tab, then you would use the delimiter option. For example, if your data looks like this:

You would use a comma as your delimiter.

If your data is not separated by a specific character, but you want to split it into columns of equal width, then you would use the fixed width option.

For example, if your data looks like this:

You would use fixed width to split the text into columns.

How To Use The Delimiter Option to Separate Text in Excel

Let’s say you have a list of addresses in one column and you want to separate them into individual columns for the city, state, and zip code. To do this, we would use the delimiter option.

First, highlight the column of data that you want to split. Then, go to the Data tab and click Text to Columns.

In the Convert Text to Columns Wizard, select Delimited and click Next.

On the next page, check the box next to Comma and clear any other boxes that are checked. Then, click Next.

On the next page, you will see a preview of how your data will look when it is split into columns. If everything looks good, click Finish.

And that’s it! Your text should now be split into columns.

End Result

Now that your data is arranged neatly into separate columns, your data format is more suited for further analysis using pivot tables!

How To Use The Fixed Width Option to Separate Text in Excel

If you want to split your text by fixed width, the process is similar. Highlight the column of data that you want to split and go to the Data tab. Click Text to Columns.

In the Convert Text to Columns Wizard, select Fixed Width and click Next.

On the next page, you will see a preview of how your data will look when it is split into columns. Place a break in between each column where you want to split the text. Then, click Next.

On the next page, you will see a preview of how your data will look when it is split into columns. If everything looks good, click Finish.

And that’s it! Your text should now be split into columns.

End Result

I hope this blog post was helpful in showing you how to use the text to columns wizard in Excel. Do consider using this simple but powerful tool in your next data mining or Excel project.

Key Takeaways:

– The text to columns wizard in Excel allows you to easily separate text into multiple columns.

– There are two main ways to use the text to columns wizard: by delimiter and by fixed width.

– If your data is separated by a specific character, such as a comma or tab, then you would use the delimiter option.

– If your data is not separated by a specific character, but you want to split it into columns of equal width, then you would use the fixed width option.

– To use the text to columns wizard, highlight the column of data that you want to split and go to the Data tab. Click Text to Columns. Then, follow the instructions in the wizard.

– Your text should now be split into columns.

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 training workshops!

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 Pivot Table Tips: Refreshing the Table After Source Data Changes

February 1, 2017 by Tyrone Pernsley

The pivot table in Excel is one of the most vital and versatile tools available. It allows you to look at your data from a wide range of customizable views. In the following guide, we explore how to update the Pivot Table after the source data changes.

There are a variety of reasons you might need to update the pivot table. Maybe you get a weekly report that needs to be added each week. Instead of recreating the pivot table, you can simply refresh it. Maybe there were errors in the source data that needed to be corrected. Again, it’s simpler to refresh than to recreate.

Let’s say you had the following spreadsheet:

Refreshing Pivot Table Image 1

And you created this pivot table for it:

Refreshing Pivot Table Image 2

Then your manager informs you of a price correction on the last 4 items. They went up $50 each. For such a minor correction, it would be a waste of time to create a whole new pivot table. Instead, you will:

  • Make the source data correction
  • Go to the tab with the pivot table
  • Go to the Data tab on the Excel ribbon
  • Select Refresh

Refreshing Pivot Table for New Data

You can also use the keyboard shortcut Alt+F5 to perform this task.

As you can see in the animation above, once you apply the refresh option, the data in the table automatically updates with the source data corrections.

We hope you now feel comfortable making corrections to your pivot table source data and applying the refresh. This is one of many tools available to help you perfect your pivot tables.

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

Excel Essentials: Adding Images to a Spreadsheet

September 28, 2016 by Tyrone Pernsley

Excel has many tools to help you master the look and feel of your spreadsheet. One feature it offers is the ability to add images to your spreadsheet. It can be an object from clip art, an original photo, downloaded images – as long as it’s in an image file, you can add it to Excel.

Let’s say you were putting together a sales report to send out to potential vendors. You are working with the following spreadsheet:

image-1

Now, your boss tells you he wants to add the company logo to the spreadsheet to give it a more polished and professional look. This is the process for adding the company logo:

adding-images-to-a-spreadsheet

  • Step 1: Prep your spreadsheet for the added image
  • Step 2: Go to the “Insert Ribbon”
  • Step 3: Click on the icon for ‘Pictures’
  • Step 4: Select the image you want to use in the dialogue box
  • Step 5: Click OK
  • Step 6: Using the Drag options on the image, resize to preferred size
  • Step 7: Double check to make sure it looks good.

You are now done with adding the company logo. Adding images to your spreadsheets can add value, relevance or just make them more fun.

We at Learn Excel Now hope you now feel confident adding images to Excel and formatting them to make your spreadsheets look great.

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 MAX and MIN Functions: Essential Tips for Success

September 21, 2016 by Tyrone Pernsley

There are far too many Excel functions for one person to learn, but there are essential functions every user should know. The MAX and MIN functions are two such functions. The MAX function allows you to find the highest number in given range. The MIN function does the opposite, providing you with the lowest number in a defined range. This quick tutorial will cover the basics on how to apply both functions.

For this brief tutorial, we will use the following dataset:

max-and-min-function-image-1

Let’s say you had this sales table and your manager wanted you to find the highest sale and lowest sale over a given time period. Using the example above, it might actually be easy to eye it up because there are only 20 rows of data. But in a real sales spreadsheet, it could range hundreds or thousands of rows.

So, we will use the MAX function. The syntax is easy:

=MAX(Number 1, number 2)

In above table, we are looking for the highest sale in column D, which means the function will look like:

=MAX(D2:D21)
max-function-1

Once the formula is entered, it will return the desired value:

max-function-2

On the flip side, the MIN function has a similar syntax:

=MIN(number 1, number2)

And again, our table is in column D, so the function becomes:

=MIN(D2:D21)
min-function-1

Once the formula is entered, Excel will return the desired value:

min-function-2

In addition to SUM, IF statements and VLOOKUP, the MAX and MIN functions are the most essential functions in Excel. We at Learn Excel Now hope you now feel confident in applying these functions to your everyday Excel use.

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

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