Learn Excel Now

Conquer the Fear of Excel

Automatically Adding Serial Numbers in Excel

by

Serial numbers are an essential part of many datasets because you can use them to identify specific entries in your sheet. Adding them manually can take time and result in errors, but there are several ways you can add these numbers automatically. 

Save yourself some time at work by learning how to add serial numbers to your spreadsheets. We’ll cover four popular methods.

Fill Series Method

If you want to add a series of number rows without doing so manually, use the Fill Series tool to generate a column with a list of numbers in it.

Follow these steps:

  • Select the cell you want to use and insert the number “1” into it
  • Click the Home tab, then hover over to Editing, then Fill, then Series.
  • The Series window will populate, and when it does, key in the following information:
    • Series In = Column
    • Step Value = 1
    • Stop Value = the number you want to end your sequence with (ex: 1,000)
    • Click OK

ROW Function Method

For this method, click on the cell A1, then edit it. Key in the =ROW() formula. You’ll then select the formula and drag it to the number you want to end your sequence with.

Adding One Method

This is another quick method to use if you want to add serial numbers in an Excel spreadsheet.

  • Click the cell you want to use to start your sequence
  • Enter the number “1” into the cell
  • In the next cell down, key in the formula =C1+1 (assuming C1 is your starting cell)
  • Click the formula and then drag it down to the number you want to end your sequence with

Pivot Table Method

If you want to add serial numbers to a pivot table, it’s important to insert an extra column in your source data. This number column will appear after the row item columns.

To accomplish this task, follow these steps:

  • Add a new column in your source data, and place a “1” in each cell
  • Create a pivot table with the data therein
  • Add the columns as values afterward
  • Right-click the column in the values section and select Value Field Settings
  • Click on Show Value As and select the running total
  • Click OK

We hope these tools help save time and enhance the quality of your data!

If you want to take your Excel skills to the next level and explore a wide range of formulas and functions, check out our workshop.

And, we’ve got you covered with an entire library of solutions that will empower you to work smarter!

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

Inserting Images in Excel Worksheets

by

Placing images into your Excel worksheets can be beneficial for several reasons. For one, doing so can help pair product photos with their inventory descriptions or add employee profile images to their spreadsheet data. Finally, if you want to insert your company’s branding to your worksheets, you can add a level of professionalism to the documents you create.

Here’s how to add images in Excel.

The IMAGE Function

To use the IMAGE function, enter the following formula into the cell of your choosing: 

=IMAGE(source, [alt_text], [sizing], [height], [width])

This will enable you to insert pictures into your cells from a source on the web, as well as add alternative text to the image.

Here’s a breakdown of the formula’s elements.

  • Source: This is the only required element to add. The rest are optional and can be used to enhance the look and organization of your worksheet. The source is the URL path of the photo file you want to upload.
  • Alt_text: Alt text is used to describe an image for accessibility purposes.
  • Sizing: Sizing controls the dimensions of the image. You can choose to fit the picture into the cell while maintaining the aspect ratio, fill the cell with the image while ignoring the aspect ratio, or customize the size of the image.
  • Height & Width: This element is used to customize the size of images, where you can key in the height and width measurements you want the image to have.

We hope this helps you incorporate images into your Excel worksheets with ease and confidence!

For more ways to level up your spreadsheets, check out our workshop.

And, we’ve got you covered with an entire library of solutions that will empower you to work smarter!

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

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

by

The XLOOKUP function is relatively new, and it was introduced to provide solutions for some of the issues that commonly occur when using the VLOOKUP and HLOOKUP functions. XLOOKUP serves as a sort of replacement for the other two functions mentioned, so if you can master using the XLOOKUP function, you’re all set when it comes to looking up pieces of data in your spreadsheets.

Keep reading to learn more about the XLOOKUP function.

XLOOKUP

The XLOOKUP function differs from both the VLOOKUP function and the HLOOKUP function in that it isn’t dependent on directions. XLOOKUP doesn’t have to search vertically or horizontally to locate and populate a specific piece of information. 

The XLOOKUP function looks like this: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Each component of the function has a specific meaning.

  • lookup_value: the information you want to locate
  • lookup_array: the list you want Excel to search for the information
  • return_array: the list you want the results from
  • [if_not_found]: the information populated if the value doesn’t exist
  • [match_mode]: exact match only setting
  • [search_mode]: search from first to last or last to first

It’s worth noting that the XLOOKUP function comes with several optional settings you can include in order to more accurately find the information you’re looking for. Because this function was designed to overcome the setbacks associated with the other two functions, many people prefer to use XLOOKUP to find data in their spreadsheets.

We hope this helps you feel comfortable using the XLOOKUP 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!

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!

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!

Creating Basic Formulas Part 3: Division

by

Excel formulas can simplify the essential mathematical operations of addition, subtraction, multiplication, and division.

In Part 2 of this series, we covered creating basic multiplication formulas in Excel. To finish this series, we’ll tackle how to divide with the program.

Division Formula

Division has the same capabilities as addition and multiplication. To divide cells, you can either type in the totals or the cell names and use the “/” sign to indicate division. If you want to divide C7 and C5, which will be 6 and 2 for this example, you can write the formula as C7/C5 or 6/2.

You can also use the QUOTIENT function, which works the same way the =SUM and =PRODUCT functions do. In the cell where you want your total to appear, type “=quotient” and choose the =QUOTIENT function. Select the cells you want to divide and hit enter.

There are an extensive number of Excel formulas that enable more advanced mathematical functions, but these four are the most essential formulas to learn for Excel beginners.

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!

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