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:

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:

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:

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:

As you can see in the above spreadsheet, all of the misspelled words were fixed:

“projcet” was changed to “project”
“prdoucts” was changed to “products”
“incrase” was changed to “increase”
“esitmated” was changed to “estimated”
“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:

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:

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

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.

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:

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

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.

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:

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:

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:

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:

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:

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

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

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.

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:

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:

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:

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

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

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:

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

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:

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:

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:

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

This 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.

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

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

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:

And then you apply the table setting:

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

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:

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

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

Excel Data Validation: An Introduction

Excel Essentials: Running Spell Check in Excel

Excel Essentials: Adding Images to a Spreadsheet

Excel Find & Replace: Building Fundamental Skills

How to Calculate a Subtotal In Excel Using the Filter

Sorting and Filtering Data With Excel

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

The Excel Fill Handle: How to Quickly Add Data

Removing Duplicates in Excel: Quick How-to Guide

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

Connect with Us

Contact

Connect with Us

Contact

WELCOME BACK!

Reset Password