Learn Excel Now

Conquer the Fear of Excel

  • Home
  • Training
    • Excel
    • MS Office
    • Outlook
    • PowerPoint
    • Word
    • OneDrive
    • Google
  • All-Access
  • eLearning
    • Excel Foundations
    • Micro Courses
      • Simply Excel
  • Resources
  • Login

Excel Data Validation: An Introduction

December 7, 2016 by Tyrone Pernsley

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

October 5, 2016 by Tyrone Pernsley

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

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

September 7, 2016 by Tyrone Pernsley

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

July 20, 2016 by Tyrone Pernsley

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

July 13, 2016 by Tyrone Pernsley

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

July 6, 2016 by Tyrone Pernsley

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

The Excel Fill Handle: How to Quickly Add Data

June 29, 2016 by Tyrone Pernsley

The Excel fill handle is one of the most convenient tools available to enter data quickly and easily. Using the fill handle, you can enter repeating or sequential content across multiple cells at a time.

This quick guide will provide you with how to use the fill handle to fill in data on your spreadsheet.

First, you need to know where and how to start with the Excel fill handle. On the bottom right corner of any Excel cell, you will see a large square:

Excel Fill Handle Image 1

When you hover your cursor right over that square, the cursor changes into a bold + mark:

Excel Fill Handle Image 2

Once your cursor shows that icon, you can click and drag the cell horizontally or vertically to fill in the data. The content of the first cell will determine what will be filled in across the other cells.

Filling In Dates

You can start with a single date or month and use the Excel fill handle to fill in the remaining months:

Excel Fill Handle GIF 1

Filling in Sequences

If you use several cells in a row with related data, you can highlight all the cells and use the Fill Handle on the remaining cell to fill in the sequence:

Excel Fill Handle GIF 2

Copy and Paste:

If you have a single piece of content and need to copy across cells, then you would use the fill handle on the first cell and it will copy it across all cells:

Excel Fill Handle GIF 3

As you can see, the Excel fill handle is an ideal tool to quickly fill in data.

We here at Learn Excel Now hope you enjoyed this week’s tips on the Excel Fill Handle. Hopefully this will save you some time as you build your spreadsheets.

Like Learn Excel Now? Subscribe above to receive our weekly tips and special offers.

Getting Social with Excel – Follow us on social media and share our content with your networks!

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.

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

Kevin – Learn Excel Now

  • 1
  • 2
  • 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