Learn Excel Now

Conquer the Fear of Excel

Merge and Manipulate Multiple Excel Sheets like a Pro

by

Working with multiple Excel sheets is common for many spreadsheet users. However, managing and consolidating data across worksheets and workbooks can be tedious and time-consuming without the right techniques. Master these tricks to merge and manipulate Excel sheets like an expert.

View Sheets Side-by-Side

Rather than constantly switching between tabs, view multiple sheets side-by-side in the same window. Go to the View tab and select “New Window” to open a second window displaying your workbook. You can tile or cascade sheets and easily refer to data across windows.

Link Sheets with 3D Formulas

Build 3D formulas to analyze data across multiple sheets. Start a formula as usual and add the sheet name followed by an exclamation point before the cell reference, like =Sheet2!A1. This pulls data from Sheet2 into your formula on the active sheet for seamless cross-referencing.

Consolidate Data with PivotTables

Easily combine, summarize, and report on data from separate sheets with PivotTables. When creating a PivotTable, check the box for “Multiple Consolidation Ranges” and add all the source sheets. The PivotTable will automatically pull and consolidate the data for flexible reporting.

Merge Tables with Power Query

For a more permanent data merge, use Power Query to combine tables from different sheets into a single master table. Load the sheets you want to merge, select the tables, go to the Transform tab, and click “Merge Tables”. Power Query will append the rows together into one unified dataset.

Link Cells Across Sheets

Rather than retyping the same data in multiple sheets, you can link cells and ranges across sheets to maintain consistency. Type “=” in a cell, then click the sheet tab and select the cell to link. The data will remain synced as it’s updated.

With these tips, you can masterfully merge, consolidate, and manage data across all your Excel worksheets. Want to take your Excel skills even further? Check out our upcoming workshop!

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

Data Cleansing in Excel: Streamlining Your Analysis Workflow

by

Data is the foundation of any analysis, decision-making process, or business strategy. However, raw data seldom comes perfectly organized and error-free. That’s where data cleaning and preparation play a crucial role in transforming messy data into valuable insights. Excel, being one of the most widely used tools for data management, offers a host of powerful features and best practices to ensure accuracy and reliability.

  1. Identify and Handle Missing Data: Missing data is a common issue that can distort the analysis. Excel provides tools like filtering and conditional formatting to identify and handle missing values. One can choose to either remove incomplete records or use imputation techniques to estimate the missing data.
  2. Remove Duplicate Records: Duplicates can introduce bias and affect the integrity of the analysis. Excel’s “Remove Duplicates” feature allows users to quickly identify and eliminate duplicate rows, ensuring the data remains clean and accurate.
  3. Data Validation: Implementing data validation rules can prevent erroneous data entry. Excel’s data validation feature allows setting criteria for data cells, ensuring users enter only valid and relevant information.
  4. Proper Formatting and Consistency: Inconsistent formatting can lead to data interpretation issues. Excel enables users to format cells consistently and convert data types as required. This ensures that numerical data is treated as numbers, dates as dates, and text as text.
  5. Handling Outliers: Outliers can significantly impact statistical analysis. Excel’s functions such as “IF,” “COUNTIF,” and “AVERAGEIF” help identify and deal with outliers effectively, preserving the accuracy of the data.
  6. Splitting and Combining Data: Sometimes, data may be combined in a single cell, leading to complications during analysis. Excel offers functions like “Text to Columns” and “Concatenate” to split and combine data elements, respectively, ensuring data is organized correctly.
  7. Remove Unnecessary Characters and Spaces: Excel’s “TRIM” function is useful for removing extra spaces, while “SUBSTITUTE” can help eliminate unnecessary characters or symbols from the data.
  8. Date and Time Formatting: When dealing with date and time data, Excel allows users to format these values consistently and manipulate them using various functions for better analysis.
  9. Protecting Clean Data: After cleaning and preparing the data, it’s essential to protect it from accidental changes. Excel offers password protection and sheet locking to safeguard the accuracy of the cleaned dataset.
  10. Document Data Cleaning Steps: Finally, it’s vital to document the data cleaning steps taken in a separate sheet or cell comments. This documentation ensures transparency and enables reproducibility in case of future analyses or audits.

In conclusion, data cleaning and preparation are vital steps to ensure accurate and reliable analysis in Excel. By following these best practices, users can transform raw and messy data into valuable insights that drive informed decision-making and business success. Remember, investing time in data cleaning ultimately saves time and resources in the long run, while ensuring the integrity and accuracy of your data-driven endeavors.

If you want to take your tackle Excel’s basics and beyond, check out our workshop.

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

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!

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!

 

Excel Basics: How To Separate Text Into Columns

by

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

by

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 Date Calculations Part 1: Finding the Difference Between Two Dates

by

Excel has several built-in date functions you can use to quickly find important information. These are known as Excel date calculations. Today, we will focus on how to find the difference, in number of days, between two different dates. We will also cover how to find a date based on number of days from a given date.

For today’s example, we will use the following spreadsheet outlining due dates for a library:

excel-date-calculations-image-1

Here, we have the due date and the current date. To enter the current date into your spreadsheet, you will use the formula =TODAY(). It will autofill with today’s date.

What we don’t have is the number of days overdue, and the overdue fee. To find that, we will:

Subtract the due date from the current date

Multiply the number of days by the fee per day

excel-date-calculations-gif-1

As you can see, Excel makes it easy to find the exact number of days and then calculate the amount owed. This process involves first finding the number of days and then separately finding the amount due. But you can also build the date calculation right into the fee calculation.

You will use the parenthesis to indicate which function you want performed first (always remember: Excel operates on the Order of Operations).

So, the first part of the calculation will be: =(B3-A3)

Then, tell the formula to also multiply by the amount owed column: =(B3-A3)*D3

excel-date-calculations-gif-2

Finding a Date Based on Number of Days

Let’s say you had the same spreadsheet, except that the original due date was missing, but you still have the number of days. Your job is to find the original due date. This is basically a reverse of finding the number of days overdue. You will enter:

=B3-C3

excel-date-calculations-gif-3

This is how you use Excel to find the number of days between two dates or vice versa. Next week, we will cover additional date functions you will want to know. We hope you found today’s lesson on Excel date calculations 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 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

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