Learn Excel Now

Conquer the Fear of Excel

VLOOKUP, HLOOKUP, and XLOOKUP Formulas Part 2: HLOOKUP Formula

by

HLOOKUP is a function that Excel users can key in to look up and retrieve data from a specific row in a table. This function searches for values in the table’s first row, then returns a value in the same column from that row if the listed conditions are met.

Read on to discover the unique characteristics of the HLOOKUP function.

HLOOKUP

In the HLOOKUP function, the H stands for horizontal, so this function performs horizontal searches from left to right.

The HLOOKUP Function looks like this: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

The components of the function have the following significance:

  • lookup_value: Like the other lookup functions, this segment identifies the piece of information you want to find
  • table_array: the table you want to search to find the specific piece of information
  • row_index_num: the row number from the table_array segment that will produce the matching value
  • range_lookup: the value that determines whether you want the function to locate an exact match or an approximate match

We hope this helps you feel comfortable using the HLOOKUP formula in Excel.

For a detailed demonstration from an expert, with exercises, and the most common questions answered, check out our workshop: Mastering Excel Lookup Functions: What Every User Should Know.

If you’d like to learn additional Excel formulas that can make your job easier, quicker, and more accurate, we’ve got you covered!

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

10 Reasons to Use Excel Tables

by

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.

For a even more excel essential from an expert, with exercises, and the most common questions answered, check out our workshop: Microsoft Excel: The Basics & Beyond for Today’s Top Professionals.

If you’d like to learn additional Excel formulas that can make your job easier, quicker, and more accurate, we’ve got you covered!

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

 

Reviewing Formulas and Functions Part 2: AVERAGE in Excel

by

Learning a selection of Excel formulas can take your reports to the next level. Mastering a few tips and tricks can not only save time for working professionals but using Excel formulas can help double-check the values listed on a spreadsheet. To simplify your work and make sure you’re presenting accurate data, take a look at how to use the AVERAGE formula.

AVERAGE in Excel

The AVERAGE function is used to determine the average value or number in a list of numbers. It effectively finds the midpoint of a number sequence. 

Using the AVERAGE Formula

To determine the average of a list of numbers in an Excel spreadsheet, use the following formula:

  • =AVERAGE(C4:C9)

You can also find the average of several number sequences by separating them with commas.

  • =AVERAGE(C4:C9, B5:B7)

We at Learn Excel Now hope you feel comfortable creating using the SUM function in Excel. Next week, we’ll wrap up the series with COUNT function in Excel so stay tuned!

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

Excel Data Validation: An Introduction

by

Excel data validation allows you to set specific criteria for the type of data that can be entered into a cell or group of cells. In addition to creating that criteria, you can write a brief message on what can be entered into the cell and error message when the wrong information is entered.

In today’s example, we will use the following customer survey as our example:

data-validation-image-1

Here, we need to set data validation so only whole numbers between 1 and 10 can be entered. To do this, we will:

  • Go to the Data tab
  • Go to Data Validation
  • Open the Data Validation Dialogue box
  • On the Settings tab, set the range (whole numbers between 1 and 10)
  • On the Input message, enter our unique message
  • On the Error Alert tab, enter your custom error message

Once you hit okay, the rules will apply. Here is a demonstration in Excel:

data-validation

As you can see, the only numbers that can be entered are whole numbers between 1 and 10. There are a variety of reasons to use Excel data validation and it depends on the situation and problem you’re trying to solve.

We here at Learn Excel Now hope you found today’s lesson on Excel data validation 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

Excel Essentials: Running Spell Check in Excel

by

Excel offers many built-in tools to help you perfect your spreadsheets. Some of these tools, however, need to be activated rather than running automatically. Today, we explore one such tool: running spell check in Excel.

When most people think Excel, the first thing that comes to mind is numbers. But text is often required in Excel to put those numbers into context. This is especially true on any type of report where you might need to add background information, explain your thought process, or just explain where the numbers on the page came from. Even something as simple as column headings, or a client name will require written text.

When submitting reports, you want the spreadsheet to be as accurate as possible and look good. Misspellings and typos simply won’t do. But unlike many Microsoft programs (Word, Outlook, PowerPoint, etc.), Excel doesn’t automatically spell check, giving you those convenient squiggly, red lines when something is wrong. This can make it extremely difficult to catch little errors.

Let’s say you had the following spreadsheet:

excel-spell-check-image-1

Maybe you can recognize all of the typos right away, maybe you can only see a few. The only way to be sure you caught all of the misspelled words is to run Spell Check:

excel-spell-check-gif

As you can see in the above spreadsheet, all of the misspelled words were fixed:

  1. “projcet” was changed to “project”
  2. “prdoucts” was changed to “products”
  3. “incrase” was changed to “increase”
  4. “esitmated” was changed to “estimated”
  5. “retruns” was changed to “returns”

Excel spell check is the perfect tool for verifying accuracy and correcting mistakes when it comes to text in Excel.

We at Learn Excel Now hope you feel comfortable checking the spelling in your text.

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

by

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 Find & Replace: Building Fundamental Skills

by

Find and Replace Featured Image (2)Excel contains tons of built-in tools designed to help you do what you need to. This week we are talking about one of those tools, the robust Find and Replace tool. In the following guide, we will explore how to use this tool and situations where it could be needed.

Simple Find Function

To find something in Excel, you use the simple Find function you use in any other program, Ctrl F. This opens the Find dialogue box where you can enter the text of what you’re looking for. However, Excel contains other built-in options in the dialogue box that allow you to go beyond the simple find function. We will explore those options later in this guide.

The important thing to remember is the keyboard combination Ctrl F at the same time.

Find Dialogue box

Replace What You Found

As you can see in the above image, the dialogue box contains a tab that states “Replace.” Once you found the items you are looking for, you can easily replace them using this tool.

Let’s say you had the following spreadsheet listing what clients each salesperson represents:

Replace Image 1

Now, your boss informs you that Cheryl Myers will be leaving the company. A new employee, Shania Reynolds, will be taking over her sales. Manually replacing Cheryl’s name with Shania’s in each instance would be tedious and time consuming (especially in larger worksheets!). Plus you could always run the risk of missing a location. Luckily, you can use the Replace tool to quickly find and replace all instances in a few clicks:

  • Use the Ctrl F keyboard combination to open the dialogue box
  • On the Find tab, enter the data you want (Cheryl Myers in this instance)
  • Click on Find All to ensure all instances are located
  • Then click on the Replace Tab
  • On the “Replace With” line, enter the new data (Shania Reynolds in this instance)
  • Click on Replace All

As you can see in the following GIF demonstrating these steps, all instances of Cheryl Myers are replaced with Shania Reynolds

Find and Replace GIF 1

Special Formatting for Replacing

The options for Excel’s Find and Replace tool don’t stop there. You can also use the tool to format specific cells. Let’s say your boss wanted you to highlight the new employee’s areas for quick reference. Again, you can do this manually, but it would be a waste of time. Instead, you could:

  • Use Ctrl F to open the dialogue box
  • Enter the data you want to find (Shania Reynolds)
  • Click Find All
  • On the Replace Tab, open the Options dialogue
  • Select Format on the “Replace With” line
  • Go to the tab that says “Fill”
  • Select the color you wish to use for highlighting (yellow)
  • Click “Replace All”

As you can see in the following GIF, the name is still in the cells, but it has been highlighted yellow.

Find and replace GIF 2

We at Learn Excel Now hope you now feel comfortable using the Find and Replace tool. This tool contains other formatting options to change the cells containing data you found. Using the tool is a matter of personal need, but there are many situations where it comes 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

How to Calculate a Subtotal In Excel Using the Filter

by

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

Sorting and Filtering Data With Excel

by

There are many built-in Excel tools to help with data management and the sorting and filtering features are among the best. The filter tool gives you the ability to filter a column of data within a table to isolate the key components you need. The sorting tool allows you to sort by date, number, alphabetic order and more. In the following example, we will explore the usage of sorting and filtering and show some advanced sorting techniques.

For today’s example, we will use the following spreadsheet:

Sorting & Filtering Image 1

As you can see, the order dates, order numbers, prices, etc. are all out of order. Let’s get started on running some sorting and filtering techniques.

Sorting Data

Let’s say you had the spreadsheet above and wanted to sort by price. This process is fairly simple. You can either highlight the whole column or even click on the first cell in the column to get started. Then you will:

  • Right click to open the menu
  • Go down to the Sort option – when hovering over Sort the sub-menu will appear
  • Click on Largest to Smallest
  • Select Expand the selection
  • Click OK

Sorting & Filtering GIf 1

The whole table has now adjusted for the sorted column. Note: when the data in one column is related to the data in the remaining columns of the table, you want to select Expand the selection. This will ensure the data in that row carries over with sorted column data.

Filtering Data

The filter feature applies a drop down menu to each column heading, allowing you to select specific choices to narrow a table. Using the above example, let’s say you wanted to filter your table by Company and Salesperson. Specifically, you want to find the number of sales Dylan Rogers made to Eastern Company.

To do this using the filter you would:

  • Go to the Data tab on Excel ribbon
  • Select the Filter tool
  • Select Eastern Company from the dropdown menu
  • Select Dylan Rogers from the Salesperson dropdown menu

Sorting & Filtering GIF 2

Boom – you now have the exact number of sales Dylan Rogers made to Eastern Company.

The Sort & Filter Tool

In addition to the right-click menu sorting option and the Filter tool on the Data ribbon, Excel has a Sort & Filter tool that allows for custom sorting.

In the following GIF, we can see how the Custom Sorting tool can be used to sort date ranges or price ranges.

Sorting & Filtering GIF 3

But notice how this example is either/or. What if you wanted to sort by date and by price? This where the Custom Sort option really comes in handy. After selecting your first sorting conditions, you can add a level to get event more accurate data:

Sorting & Filtering GIF 4

As you can see, Excel offers a variety of sorting and filtering tools to help you refine your data and keep it organized. We hope you found today’s tips useful. Now go out there and get your data sorted!

Use Learn Excel Now to help with all your Excel questions and training needs.  We’re not just experts in Excel, there is content, free resources, and training courses available for Word, Outlook and more.

Download our free templates and guides

Receive live webinars from industry experts

Train at your own pace with our on-demand training

 

Essential Excel Functions: How to Use CONCATENATE

by

Excel possess many formulas and functions to help you perform essential tasks. The CONCATENATE function is one of most useful. It allows you to combine data from two or more cells into a single string. There are many applications for this function. It is particularly useful when pulling an Excel spreadsheet from a database where pieces of information are broken up over different columns.

To get an idea of how to use this function, let’s take the example of phone numbers. In the following example, the area code, 3-digit prefix and 4-digit ending are all broken up in different columns:

Excel CONCATENATE Image

Now, if we want to combine these into a single cell to make the phone numbers easy to view, copy and paste, we will use CONCATENATE. Here is how the formula looks:

= CONCATENATE (Text1,Text2,Text2,…)

Notice you need to identify each individual cell or text making up each component of the function. Here is how it looks when combining the columns to yield the phone numbers:

Excel Concatentate Gif 1

You can now use the Fill Handle to copy the function all the way down yielding phone numbers in the remaining rows.

Let’s take a look at another example. One of our fans, Susan, brought an interesting query to us a few weeks ago. She was pulling a list of email addresses from her company’s database.

Now, Susan told us that the company’s email worked like this: Firstname.lastname@companydomain.com

Excel Concatenate Image 2

As you can see in the image above, the dot between first name and last name and the @ symbol between last name and company domain are missing.

So, to help Susan use the CONCATENATE, we first need to prep the data. Here are the steps to take:

  • Add a column between First and Last Name
  • Add a period in the new column – use the Fill Handle to copy it all the way the down
  • Add a column between Last Name and Company domain
  • Add the @ symbol and use the Fill Handle to copy it all the way down
  • In the first open cell in the top row, enter the CONCATENATE function
  • Use the Fill Handle to copy it all the way down

Excel Concatenate Gif 2

As you can see in the above image, the function was used successfully to create the email addresses.

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

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

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.

Connect with Us

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