Loading...

Follow MyExcelOnline Excel Blog Tutorials on Feedspot

Continue with Google
Continue with Facebook
or

Valid

Have you had challenges with constantly Refreshing a Pivot Table?

People forget that each time your data source gets updated that you will also need to manually Refresh your Pivot Table in order for it to get updated and show the changes made.

A lot of people ask if there is a way to automatically Refresh a Pivot Table, which I totally get.  Automation is why we use Excel, right!

Here I show you a couple of ways that you can automatically Refresh a Pivot Table.

   1. REFRESH PIVOT TABLE UPON OPENING:

This is a great feature and one that most people don’t know about.

It allows you to Refresh your Pivot Tables as soon as you open up your Excel workbook.

This is great if your Pivot Table’s data is linked to another workbook that gets updates by your colleagues and you only get to see the Pivot Table report.

STEP 1: Right Click in your Pivot Table and choose Pivot Table Options:

STEP 2: Select the Data tab and check the “Refresh data when opening the file” checkbox and OK

Now each morning that you open up your Excel workbook, you can be sure that the Pivot Table is refreshed!

   2. AUTOMATIC REFRESH EVERY X MINUTES:

If you have your data set linked in an external data source, you can auto-refresh every x minutes.

Your data can be stored in an external data source such as Access, a Website, SQL Server, Azure Marketplace etc

STEP 1: If your data is stored externally, you will need to click in your Pivot Table and go to Properties (this will only be enabled for selection if you have an external data source)

STEP 2: This will open up the Connection Properties and you will need to select the Refresh every checkbox and manually set the time & press OK.

You can now sit back and enjoy a cup of coffee whilst your Pivot Table gets updated every few minutes:)

Automatically Refresh a Pivot Table

Helpful Resource:

Searching...

The post Automatically Refresh a Pivot Table appeared first on Free Microsoft Excel Tutorials.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.

But what if your data source is not in your Excel spreadsheet but located on your desktop?

If it’s inside a CSV file – Comma Separated Values which is denoted by a .csv file extension & where the columns are separated by commas – it’s very easy to import data from csv and right into Power Query! 

It’s very common nowadays to get data in the comma-delimited format.

Let’s suppose you have this set of data from the csv file:

STEP 1:

Using Excel 2016 (screenshot below)

Go to Data > New Query > From File > From CSV

Using Excel 2013 or Excel 2010

Go to Power Query > From File > From CSV

Select the csv file that contains the data.  Click Import.

A preview of the csv data will be shown.  If it looks good, press Edit.

STEP 2: This will open up the Power Query Editor.

Go to Home > Transform > Use First Row As Headers

This will give your table the correct Column Headers.

STEP 3: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the imported table.

You now have your new table from the csv file!

Import Data from CSV in Excel

HELPFUL RESOURCE:

Searching...

The post Import Data from CSV Using Power Query or Get & Transform appeared first on Free Microsoft Excel Tutorials.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

The #1 complaint that I get from Pivot Tables is “Why do my values show as a Count of rather than a Sumof ?”

Well there are three reasons why this is the case:

1. There are blank cells in your values column within your data set; or

2. There are “text” cells in your values column within your data set; or

3. A Values field is Grouped within your Pivot Table.

1. BLANK CELL(S):

So if you have at least one blank cell in a Values column, Excel automatically thinks that the whole column is text based.  Pretty stupid but that’s the way it thinks.

    2. TEXT CELL(S):

Also if you have a cell that is formatted as Text within your Values column, then it will also cause it to Count rather than Sum.  This usually happens when you download data from your ERP or external system and it throws in numbers that are formatted as text e.g. 382821P

We get the annoying Count of Sales below:

Have a look at the following tutorials that show you how to locate blank cells: Find Blank Cells In Excel With A Color

EXCEL FIX:

STEP 1: You will need to enter a value or a zero within this blank or text formatted cell(s)

STEP 2: Go over to your Pivot Table, click on the Count of….  and drag it out of the Values area

STEP 3: Refresh your Pivot Table

STEP 4: Drop in the Values field (SALES) in the Values area once again

    3. GROUPED VALUES:

Let’s say that you put a Values field (e.g. Sales) in the Row/Column Labels and then you Group it.

When you drop in the same Values field in the Values area, you will also get a Count of…

EXCEL FIX:

STEP 1: Right Click on the Grouped values in the Pivot Table and choose Ungroup:

STEP 2: Drag the Count of SALES out of the Values area and let go to remove it

STEP 3: Drop in the SALES field in the Values area once again

It will now show a Sum of SALES!

N.B. Sometimes you will need to locate the Pivot Table that has the Grouped values.  The SALES field may not be evident that it is Grouped, especially if it is not selected in the Row/Column labels.

You may need to drag and drop this field from the PivotTable Fields and into the Row/Column Labels area to confirm that it is Grouped.

Helpful Resource:

Searching...

The post Count VS Sum in Pivot Tables appeared first on Free Microsoft Excel Tutorials.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview