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
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

What does it do?

Counts the number of cells that are blank

Formula breakdown:

=COUNTBLANK(range)

What it means:

=COUNTBLANK(range of cells to check)

Do you have a scenario where you want to count the number of cells that are blank in your Excel data?

If you are auditing your data and want to make sure that a blank cell is actually blank (and doesn’t contain an invisible character), then this formula is for you.

There is a simple way to count blank cells using Excel’s COUNTBLANK formula!

I explain how you can do this below:

STEP 1: We need to enter the COUNTBLANK function in a blank cell:

=COUNTBLANK(

STEP 2: The COUNTBLANK arguments:

range

What is the range of values that you want to check how many are blank?

=COUNTBLANK(B9:C12)

You now have your count of values that are blank! There are 3 blank values!

CountBlank Formula in Excel

HELPFUL RESOURCE:

Searching...

The post CountBlank Formula in Excel appeared first on Free Microsoft Excel Tutorials.

Read Full Article
Visit website
  • 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
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Do you have a lot of numbers with an uneven number of digits in your Excel list?

Do you want to make them uniform by adding leading zeros to them?

Well, it’s a pain to add zeros in front of them one by one!

Thankfully, Excel allows you to add leading zeros with one single formula!

STEP 1: We need to enter the TEXT function in a blank cell:

=TEXT(

STEP 2: The TEXT arguments:

value

What is the value that you want to add leading zeros on?

=TEXT(B9,

format_text

How many leading zeros do you need?

If we want our number to be 6 digits long, then type in 6 zeros: “000000”

=TEXT(B9, “000000”)

Apply the same formula to the rest of the cells by dragging the lower right corner downwards. Your leading zeros are now ready!

How to Add Leading Zeros in Excel

Helpful Resource:

Searching...

The post Add Leading Zeros in Excel appeared first on Free Microsoft Excel Tutorials.

Read Full Article
Visit website
  • 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
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

What does it do?

Counts the number of cells that are non-blank/non-empty (including empty text “”)

Formula breakdown:

=COUNTA(value1, [value2], …)

What it means:

=COUNTA(value or range of cells to check, [value or range of cells to check], …)

Do you have a scenario where you want to count the number of cells that are non-blank or not empty?

I’m sure you do!  There is a simple way to count this with Excel’s COUNTA formula! 

This formula counts everything: numbers, text, non-empty text “”, you name it!

I explain how you can do this below:

STEP 1: We need to enter the COUNTA function in a blank cell. Notice there are 6 non-blank cells in here:

=COUNTA(

STEP 2: The COUNTA arguments:

value

What is the value or range of values that you want to check how many are non-blank?

=COUNTA(B9:C12)

You now have your count of values that are non-blank! There are 6 non-blank values!

CountA Formula in Excel

HELPFUL RESOURCE:

Searching...

The post CountA Formula in Excel appeared first on Free Microsoft Excel Tutorials.

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

A lot of Excel users get confused when they are recording an Excel Macro and they get prompted where they want to store their Excel Macro in?

Store Macro In

You have 3 options:

* Personal Macro Workbook

* New Workbook

* This Workbook

During the initial setup of recording a macro a prompt will present itself asking where the macro should be stored.  This option ultimately determines not only where the macro will be saved, but also where you can access the macro and where it can be used.

This Workbook

The “THIS WORKBOOK” option will create a module within the active workbook where Excel will save the macro (VBA Code). By storing the macro in the active workbook, the macro can be used within the that workbook or if that workbook is open.

Since the macro is contained with the workbook it was created within, if the workbook is sent to another Excel user or placed on a network drive, the macro will be there as well and other users can use that macro when that workbook is open on their system.

Files that contain a macro or VBA code must be saved as a .XLSM (MACRO ENABLED WORKBOOK) or .XLSB file.

Some Common Uses:

  • Macros that are specific to a workbooks data
  • Macros that make data connections and refreshes of data contained with the workbook
  • Macros that clean up data specific to the workbook
Personal Macro Workbook

The PERSONAL MACRO WORKBOOK is a hidden workbook located on a machine that contains Microsoft Excel.

The workbook is always open, but, hidden from view by default.

If a macro is stored in the PERSONAL MACRO WORKBOOK, this macro can be used within any workbook on the computer that contains that personal macro workbook.

Macros stored in a personal macro workbook are not typically shared through normal exchange of Excel files. To share a macro contained within the personal workbook, one would need to explicitly share their personal macro workbook, export the module that contains the macro or copy the code to another file and send that new file.

Some Common Uses:

  • One-time setup macros that can be used on multiple workbooks
  • Macros that are more specific to an individual job that isn’t shared among others
  • Repetitive tasks necessary across multiple Excel files
New Workbook

A NEW WORKBOOK creates a brand new workbook at time of recording. This new workbook will contain the macro and all of the VBA code.

In order to use these macros you must open the new workbook that Excel created.

Not as common as the other two!

Searching...

The post Where To Store Your VBA Macro In? This Workbook or Personal Macro Workbook appeared first on Free Microsoft Excel Tutorials.

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

I’m excited to announce that we just recorded our latest free webinar on Excel Macros & VBA and you are invited to view this now:

Learn How To Record MACROS & Write VBA Code within 1 HOUR!

In this webinar you will learn:

  • How to record a Macro and create interactive buttons to run it!
  • How to automate your repetitive Excel tasks using the VBA Editor!
  • VBA code by way of simple explanations of the VBA coding language that even my 5 year old understands!
  • Saving and sharing your Macros!
  • Receive a FREE BONUS Macros & VBA Cheat Sheet when you sign up.

The webinar will be held between Tuesday the 27th of February and Wednesday the 7th of March!  The times are shown on your local timezone, so please click the link below to register for a day and time that works for you..

Searching...

The post Free Excel Macros & VBA Webinar Training appeared first on Free Microsoft Excel Tutorials.

Read Full Article
Visit website
  • 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?

If it’s inside a text file, it’s very easy to import data from text and right into Power Query!

Let’s suppose you have this set of data from a text file:

STEP 1:

Using Excel 2016 (screenshot below)

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

Using Excel 2013 or Excel 2010

Go to Power Query > From File > From Text

Select the text file (with extension .txt) that contains the data.  Click Import.

A preview of the text 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 text file!

Import Data from Text Using Power Query or Get & Transform

HELPFUL RESOURCE:

Searching...

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

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

Have you ever cleared, deleted or replaced your Pivot Table data/items but they still shows inside your Pivot Table filters?

What gives??

Well, you can easily clear your Pivot Table’s old items from your Pivot Table’s memory or cache.

In our example below we have our Pivot Table with the Years showing in the Column area (2014, 2012, 2013):

STEP 1: Below is our data source and we want to replace the year 2012 with 2013, effectively only showing the years 2014 & 2013.

Go to Home > Find & Select > Replace

Let us replace the year 2012 with the year 2013.  Click Replace All.

STEP 2: Go back to your Pivot Table.  Right click and select Refresh.

We have technically deleted the year 2012 records, so they should be gone from our Pivot Table, right?

Hmm.. Looking good, the year 2012 is now gone from our Pivot Table!

BUT WAIT! 

Clicking on the Column Labels drop down list, the Year 2012 is still there!  Bloody hell!

STEP 3: Let us fix this!  Go back to your Pivot Table > Right click and select PivotTable Options.

STEP 4: Go to Data > Number of items to retain per field.

Select None then OK.  This will stop Excel from retaining deleted data!

STEP 5: Go back to your Pivot Table.  Right click and select Refresh.

Click the Column Labels drop down list, and the Year 2012 is now gone!  Problem fixed!

How To Clear & Delete Old Pivot Table Items

Helpful Resource:

Searching...

The post Clear & Delete Old Pivot Table Items appeared first on Free Microsoft Excel Tutorials.

Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • 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