Learn Excel Now

Conquer the Fear of Excel

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

by

VLOOKUP is a relatively common Excel function that aims to simplify locating a specific piece of information located within a spreadsheet. For example, if you have a series of insurance policy numbers and the insured individual’s name listed in various cells, VLOOKUP can help you locate information in a specific column instead of scouring through mountains of data manually.

Continue to learn about the capabilities of the VLOOKUP function.

VLOOKUP

In the term VLOOKUP, the V stands for vertical, so this function performs vertical searches from the left side of the spreadsheet to the right.

The VLOOKUP function looks like this: =VLOOKUP(lookup_value, table_array, column_index, [match_model])

Let’s break down the components of the function.

  • lookup_value: the information you want to locate
  • table_array: the location where you want to look for your information
  • column_index: the number associated with the column from the table_array section
  • [match_mode]: This value should usually be set to 0 if you want the output to populate only if the VLOOKUP feature finds an exact match

We hope this helps you feel comfortable using the VLOOKUP 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 3: COUNT in Excel

by

Most working professionals want to save time when writing reports and copying data into spreadsheets, yet worrying about making mistakes can slow progress to a crawl. If you want to save time and make sure you’re recording accurate data, mastering a few Excel formulas is worth your effort.

Read on to learn about the COUNT formula and how it can help you at work.

COUNT in Excel

Instead of manually counting the number of cells being used in an Excel spreadsheet, the COUNT function can provide you with a quick, accurate answer.

Using the COUNT Formula

To use the COUNT function, there are two options, and each one depends on what you’re trying to count. Counting the cells that contain numeric values can be done this way:

  • =COUNT(C4:C9) – this will provide you with the number of cells in this sequence that have numbers in them (if all of them do, the answer is 6)

Counting the cells that simply aren’t blank (as in, they can have numbers, letters, or symbols in them), you would use this formula:

  • =COUNTA(C4:C9) – if 3 cells contain numbers, one contains letters, and two are blank, the value you would get is 4)

If you’d like to learn more Excel formulas that can make your job easier, quicker, and more accurate, stay tuned. We’ll steadily release an Excel series in the form of several short, simple blog posts.

We at Learn Excel Now hope you feel comfortable creating using the SUM, AVERAGE, and COUNT functions in Excel.

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!

Reviewing Formulas and Functions Part 1: SUM in Excel

by

Mastering Excel formulas can make a world of difference for working professionals. Not only does using formulas save time, but it also ensures that the figures you’re calculating are correct based on the data in your spreadsheet.

Read on and we’ll show you how to use the SUM formula correctly.

SUM in Excel

The SUM function is used to add the values of various cells on an Excel spreadsheet. This option reality simplifies things for working professionals who want to save time and avoid possible mistakes that come from copying data onto a calculator.

Using the SUM Formula

There are a couple of ways to use the SUM formula in Excel. You can either add the values of individual cells or add the values of a cell sequence. 

  • =SUM(C4:C9) – this version is used to add the values of one cell through another in a sequence
  • =SUM(C4, C9) – this version is used to add the values of one cell and another, not in a sequence

We at Learn Excel Now hope you feel comfortable creating using the SUM function in Excel. Next week, we’ll cover the AVERAGE function in Excel so stay tuned!

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

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