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

Merge and Manipulate Multiple Excel Sheets like a Pro

September 28, 2023 by Madeleine Moucheron

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!

Automatically Adding Serial Numbers in Excel

May 25, 2023 by Madeleine Moucheron

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

May 5, 2023 by Madeleine Moucheron

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!

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 Basics: Adding, Editing & Formatting Comments

August 31, 2016 by Tyrone Pernsley

In Excel, you can add a comment to any cell. This could be a note to yourself, a reminder, a correction, a question – anything that is useful. Using Excel comments is particularly useful for workbooks that are used to share information and to collaborate on projects. At times though, you might want to go beyond the basic comment format and give your comments more character.

First, let’s explore the basic comment. To add a comment, you will:

  • Right click on the cell you want to comment on
  • On the menu, select comment
  • Enter your comment in the comment box that opens

Once you click out of it the comment will be part of the cell

Excel Comment Gif 1

Any cell with a comment has a small, red triangle in the upper right hand corner.

Now if anyone opens this workbook they will be able to view what you wrote. This is the basic comment format:

Excel Comment Basic

Formatting Comments

As with most things in Excel, you can do more with your comment than what the default format has to offer. To open editing for your comment, you will:

  • Either create a new comment or click on Edit Comment in the menu
  • Once the comment box is open, right click on outer edge of the box
  • Select Format Comment

Once you’ve open the format comment dialogue box, you have several options:

Excel Comment Menu

From there, you can make whatever changes you see fit:

Excel Comment Gif 2

Why Change Excel Comment Formatting?

There could be several reasons to change the formatting for comments:

  • Personal preference
  • Color coding types of comments
  • Adding urgency or priority to comments
  • And many others

We here at Learn Excel Now hope you found this week’s tip on Excel comments 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 Formatting Essentials: Hiding Extra Rows & Columns

August 24, 2016 by Tyrone Pernsley

Removing Lines In Excel Featured ImageToday’s Excel blog post comes directly from a Learn Excel Now customer who was having trouble formatting her spreadsheet. She wanted to hide the extra rows and columns of the spreadsheet grid after creating a chart.

Here is what she wrote to Learn Excel Now:

“I produce weekly reports for my boss. He likes them to be in a 3D Pie Chart. But I can never seem to format it correctly. He showed me the report my coworker Carol sends him. It’s just a single chart – there is no spreadsheet behind it! It looks so clean and neat, like a single image. I want to be able to do that.”

And she can. For today’s example we will take the following spreadsheet and create a chart. Then we will format that chart so that it is the single image in the worksheet.

Removing Lines in Excel Image 1

To create the chart:

  • Highlight the group of cells you want to chart
  • Go to the Insert tab on the home ribbon
  • Select the type of chart you want to use (3D Pie Chart in this case)

To make the chart the single image on the worksheet:

  • Expand the chart to cover the underlying data in the spreadsheet
  • Click on the first column visible to the right of the chart
  • Enter the keyboard shortcut Ctrl+Shift+à (right arrow key) to go to the last column
  • Open the right click menu and select Hide
  • Click on the first row visible underneath the chart
  • Enter the keyboard shortcut Ctrl+Shift+Down Arrow Key to go to the last row
  • Open the right click menu and select Hide

You have now hidden all of the rows and columns in the spreadsheet, leaving the chart as the lone visible image on the worksheet. Here is a Gif showing what to do:

Removing Lines in Excel Gif 1

Using the keyboard shortcuts to go to the last row and column can come in handy in many situations. Anytime you want to make the spreadsheet a close off image, you can hide those rows and columns.

We here at Learn Excel Now hope you feel comfortable using this convenient formatting tool.

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

Essential Excel Skills: Protecting an Excel Worksheet

August 17, 2016 by Tyrone Pernsley

Protecting an Excel WorksheetThere are various skills everyone should learn in Excel. One of those skills is protecting an Excel worksheet. Excel allows you to add protection to a worksheet or workbook to prevent other users from making changes to your worksheet. This is particularly useful when you have an Excel workbook that multiple people use at your company.

Protecting an Excel worksheet is fairly straightforward.

Here is what you need to do:

  • Right click on the sheet tab to open the menu
  • Select Protect Worksheet
  • Set your password
  • Select the individual items you want to limit
  • Click Okay
  • Re-enter your password

Here is a quick demonstration:

Protecting an Excel Worksheet

There are several reasons to protect a worksheet. Let’s say you have a weekly sales log that has formulas and functions built into. You want your sales team to add information but don’t want them changing formulas – on accident or on purpose. So, you add protection to prevent them from being able to change anything.

Even for something as simple as not wanting someone to change your formatting, protecting an Excel worksheet can come in handy.

We here a Learn Excel Now hope you now feel comfortable protecting an Excel worksheet. This essential Excel skill will help in ensuring your data is always what you expect it to be.

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

Removing Duplicates in Excel: Quick How-to Guide

June 22, 2016 by Tyrone Pernsley

Removing Duplicates in Excel Image 2This Excel tip from Learn Excel Now is on removing duplicates in Excel. The request is directly from a Learn Excel Now fan who asked us how to quickly remove duplicates. She told us:

“I pulled a list of all company sales from our database. It exported in Excel. The list is all of our sales, but many of our clients buy multiple products several times through the year. Yesterday, my boss asked me ‘how many unique clients have purchased in the last month?’ Those figures aren’t available because we’ve always only counted total sales. I started counting the individual companies 1-by-1, but there are so many of them. I know there has to be a way to remove the duplicates so I can see each company listed only once. Can you help me?”

So, this post was designed for one of our fans – let’s call her Rachel – and anyone else who wants to know about removing duplicates in Excel.

The following table contains multiple cases of duplicates. Similar to Rachel’s request, they are the names of companies in a sales document.

ScreenHunter_239 Jun. 22 13.50

Now, let’s get in there and remove those duplicates! Here’s how you do it:

  1. Highlight the column(s) or row(s) containing the duplicate values you want to remove
  2. Go to the ‘Data’ Tab on the Excel Ribbon
  3. Find the “Remove Duplicates” tool and click it
  4. Verify the table range on the dialogue box OR select the correct range on the dialogue box

Removing Duplicates In Excel Gif

If there are more values in the table but you want to stick to a column or row, select “Use Current Selection

Excel will automatically remove all duplicates. Now you and review and count the total unique values within the range.

We here at Learn Excel Now hope you now feel confident in removing duplicates in Excel. This useful tool is great for running quick analyses, finding unique values and cleaning up datasets for other functions or formatting.

Like Learn Excel Now? Sign up for the e-newsletter to receive weekly Excel tips and special offers just for you!

And don’t forget to follow us on social media and share our Excel tips with your networks!

Kevin – Learn Excel Now

Creating Excel Tables: Quick Tip to Get Up-to-Speed

June 15, 2016 by Tyrone Pernsley

Excel provides you with many preset formatting options that you simply need to click to activate. One of the most common and most useful is to set your data as a table. There are many advantages to organizing your data in a table. It makes it easier to sort and filter important data, find totals automatically and create charts and graphs.

Setting your data as a table is fairly easy. First, you take your basic dataset:

Tables Image

And then you apply the table setting:

Excel Tables Gif 1

As you can see from the above Gif, there are many table styles to choose from:

Excel Tables Image 2

This gives you the power to customize your Excel table to your preference.

Pro tip: you can add more customization to your formatting by using the Cell Styles tool to add colors, text formatting and other elements to make your table pop:

Excel Tables Image 3

Once you have your table formatted, you can us the dropdown menu to quickly filter information:

Tables Image 1

You also have the ability organize your dataset in a variety of ways.

We here at Learn Excel Now hope you enjoyed this week’s quick tip on Excel Tables.

If you want to take your Excel skills to the next level and tackle the foundations of excel , check out our self-paced course.

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

Kevin – Learn Excel Now

Excel Number Formatting Special: Phone Numbers

June 1, 2016 by Tyrone Pernsley

Excel gives you the power to format your numbers in a ton of different ways. Today’s post is focusing on one specific, special format: phone numbers. We here at Learn Excel Now are continuously surprised by the number of people who say they didn’t know this was an Excel Feature. So, to help make sure everyone using Excel knows how to create special number formatting, we created the following quick tip.

Let’s say you exported a customer list from your database in an Excel file. There is no formatting in the new file and looks a bit like this:

Excel Number Formatting Image 2

So, the first thing you do is automatically expand the columns and possibly add a bit of color to the spreadsheet so it looks like this:

Excel Number Formatting Image 1

Now, in order to change the numbers to phone numbers, you will need to:

  • Highlight the cells containing the data you want to format
  • Click on the dropdown button on the Number tab of the Home Ribbon

Number Formatting

  • On the Format Cells dialogue box that opens, click Special and then Phone Number

Excel Number Formatting Image 3

Once you click Okay, the cells automatically format as phone numbers:

Excel Number Formatting Special - Phone Numbers

You will see there are other options on the Special formatting:

  • Zip Code
  • Zip Code + 4
  • Phone Number
  • Social Security

Any of these options will allow you to format the numbers in an easy to read way.

We hope you enjoyed this week’s quick tip on formatting Excel for numbers.

Like Learn Excel Now? Sign up for our newsletter to stay up-to-date on all things Excel?

And don’t forget to follow us on social media and share our Excel tips with your social networks!

Kevin – Learn Excel Now

  • 1
  • 2
  • 3
  • Next Page »
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