excel

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 Formulas: How to Use the LEFT and RIGHT Functions

November 2, 2016 by Tyrone Pernsley

This demonstration covers how to use the LEFT and Right Functions in Excel. These are text functions. In the LEFT function, you can pull a set number of characters out of a cell into another cell starting at the leftmost point. The RIGHT function performs the same except starting at the rightmost point.

Both functions are related to the MID function, which we covered back in September. At the time, one of our customers asked for a demo on the MID function. Since then, we’ve had several follow up requests on the LEFT and RIGHT.

For today’s demonstration, we will be using the following spreadsheet:

In this example, Column A contains a 6-digit number hyphenated with a 4-digit number. The 6-digit number is the order number and the 4 digit is the client ID. Your job is to separate the order number and client ID into Columns C and E, respectively.

How is this done?

For the order number, we will use the LEFT function. We know the code is 6 digits, so we have the number of characters. So in cell C2, you will enter the following formula:

=LEFT(A2,6)

For the client ID, we will use the RIGHT function. In cell E2, you will enter the following formula:

=RIGHT(A2,4)

LEFT Function Syntax:

=LEFT(Destination Cell, Number of Characters)

This tells Excel: Starting on the left of this specified cell, copy to this many characters.

RIGHT Function Syntax:

=RIGHT(Destination Cell, Number of Characters)

This tells Excel: Starting on the right of the specified cell, copy to this many characters.

As you can see in the above demonstration, once the functions have been entered, they can be copied all the way down to finish filling in the data.

We at Learn Excel Now hope you feel comfortable using the RIGHT and LEFT functions.

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 MAX and MIN Functions: Essential Tips for Success

September 21, 2016 by Tyrone Pernsley

There are far too many Excel functions for one person to learn, but there are essential functions every user should know. The MAX and MIN functions are two such functions. The MAX function allows you to find the highest number in given range. The MIN function does the opposite, providing you with the lowest number in a defined range. This quick tutorial will cover the basics on how to apply both functions.

For this brief tutorial, we will use the following dataset:

Let’s say you had this sales table and your manager wanted you to find the highest sale and lowest sale over a given time period. Using the example above, it might actually be easy to eye it up because there are only 20 rows of data. But in a real sales spreadsheet, it could range hundreds or thousands of rows.

So, we will use the MAX function. The syntax is easy:

=MAX(Number 1, number 2)

In above table, we are looking for the highest sale in column D, which means the function will look like:

=MAX(D2:D21)

Once the formula is entered, it will return the desired value:

On the flip side, the MIN function has a similar syntax:

=MIN(number 1, number2)

And again, our table is in column D, so the function becomes:

=MIN(D2:D21)

Once the formula is entered, Excel will return the desired value:

In addition to SUM, IF statements and VLOOKUP, the MAX and MIN functions are the most essential functions in Excel. We at Learn Excel Now hope you now feel confident in applying these functions to your everyday Excel use.

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 Pivot Table Tips: Refreshing the Table After Source Data Changes

Excel Formulas: How to Use the LEFT and RIGHT Functions

Excel Essentials: Adding Images to a Spreadsheet

Excel MAX and MIN Functions: Essential Tips for Success

Sitemap

Connect with Us

Contact