Excel Tools

Excel Pivot Table Tips: Refreshing the Table After Source Data Changes

February 1, 2017 by Tyrone Pernsley

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:

And you created this pivot table for it:

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

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

December 14, 2016 by Tyrone Pernsley

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:

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

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

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

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

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

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

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

Excel Merge & Center – What It is & How to Use It

June 8, 2016 by Tyrone Pernsley

The following article shows you how to use the Excel Merge & Center tool.

The spreadsheet software of Microsoft Excel is best known for crunching numbers. This remains the primary reason people use Excel. But often times, you need text to accompany those numbers to make sense of what the numbers mean. Whether it’s column headings, naming the worksheet itself, itemization or important notes, adding text to Excel is a vital component of making your Excel spreadsheets be professionally viable.

Sometimes adding text can create formatting issues. Fortunately, Excel provides formatting options to make your spreadsheet accommodate text without compromising the number formatting. One such tool is the Merge & Center tool. This is perfect for text that runs over multiple cells. Without changing the Excel width or height of the individual cells in a column or row, you can merge all the cells together. The text will also automatically expand.

Let’s take the following spreadsheet as an example. The name of the spreadsheet has been added to the spreadsheet:

Notice how the text for the name of the spreadsheet runs from A1 to C1. The table, however, is already formatted so that the columns are the right width for the Column Headings. Adjusting column width for the name of the spreadsheet throws everything off:

This is where the Excel Merge & Center tool comes into play. The following Gif will show you how to use the tool:

Now, as you can see above, the text for the spreadsheet heading runs the width of the table. But none of the columns or rows containing table data had to adjust. This is now a fixed heading.

From there, you can format the color, text size, etc. to get the heading exactly how you want it.

We here at Learn Excel Now hope you now feel comfortable using the Excel Merge & Center option.

Like Learn Excel Now? Follow us on social media and sign up for the newsletter!

Don’t forget to share our tips with your friends and colleagues!

Kevin, Learn Excel Now.

Excel Essentials: How to Remove Duplicates in Excel – Video

April 6, 2016 by Tyrone Pernsley

Clean, readable data makes Excel much easier to use. A common struggle is how to remove duplicates in Excel. Having too many duplicates causes inaccuracies in reporting and analyzing data. But scrolling through your data to hunt and peck duplicates is tedious and time-consuming – especially when you have hundreds of rows of data.

Even using the Find function (keyboard shortcut: Ctrl+F) can take forever. But Excel provides built-in tools to help you with this.

Please enjoy the following video on how to remove duplicates in Excel:

<a href="https://vimeo.com/161782320">LEN-WebVideo_ExcelBasics_Removing Duplicates</a> from <a href="https://vimeo.com/learnexcelnow">Learn Excel Now</a> on <a href="https://vimeo.com">Vimeo</a>.We hope you enjoyed this free video on how to remove duplicates in Excel.If you would like to discover all of the essential Excel skills for the modern workplace, please join us for:<a href="https://www.learnexcelnow.com/product/excel-essentials-needed-skills-todays-workplace/" target="_blank" rel="noopener noreferrer">Excel Essentials: Most Needed Skills in Today’s Workplace</a> Wednesday, April 13, 2016Like Learn Excel Now? Follow us on social media!Kevin – Learn Excel Now

We here at Learn Excel Now hope you know feel confident in your ability to remove duplicates in Excel

To discover essential tips like this and so much more, please join us for:

Excel Essentials: Most Needed Skills in Today’s Workplace
Wednesday, April 13, 2016

In this live, online event, our Excel expert instructor will cover the key Excel skills you need for workplace success.

Like Learn Excel Now? Sign up for our weekly tips, announcements and offers!

Getting social with Excel – don’t forget to follow us on social media and share our content with your networks!

Kevin – Learn Excel Now

How to Copy an Excel Worksheet Within the Workbook or to a Different Workbook

March 23, 2016 by Tyrone Pernsley

You created your ideal worksheet. It’s formatted exactly how you want it. The formulas are in place, the data has been filled in and everything looks amazing. Now, your boss asks you to create the same worksheet but add a few new elements…while retaining the original worksheet. You feel that sudden angst rise up as you think about redoing all of that work over again. There has to be a better way…right?

Of course there is. You can copy an Excel worksheet within the workbook (or for another workbook) with just a few flicks:

Right Click on the tab you want to copy
Select the “Move or Copy…” option
In the dialogue box that opens, make sure the workbook you want created is selected
Click on the box that says “Create a Copy”
Click OK

And viola – your copied worksheet is created!

Now, let’s say you wanted to copy a worksheet to a different workbook altogether. Once you right click on the tab for the worksheet you want to copy, you will:

Find the drop down at the top of the dialogue box that says “To Book:”
And then on that dropdown menu, select the workbook you want to copy it to
Click OK again and boom – you’ve now copied the worksheet to another workbook.

We here at Learn Excel Now hope you now feel confident in your ability to copy an Excel worksheet both within the existing workbook and to outside workbooks. This convenient tool allows you to replicate important data and spreadsheets without compromising the original data.

Like Learn Excel Now? – Follow us on Social Media and share with your colleagues and friends.

Kevin – Learn Excel Now

Excel Pivot Table Tips: Refreshing the Table After Source Data Changes

Excel Date Calculations Part 1: Finding the Difference Between Two Dates

Excel Data Validation: An Introduction

Excel Essentials: Running Spell Check in Excel

Excel Find & Replace: Building Fundamental Skills

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

The Excel Fill Handle: How to Quickly Add Data

Excel Merge & Center – What It is & How to Use It

Excel Essentials: How to Remove Duplicates in Excel – Video

How to Copy an Excel Worksheet Within the Workbook or to a Different Workbook

Sitemap

Connect with Us

Contact