Tip of the month (3)

Tip of the month (3)

At the beginning of each month, I publish a tip to make easier & quicker your use of Excel.

After the Excel shortcuts and the quick access toolbar, this 3rd tip of the month is dedicated to data cleansing.

Have you ever faced this situation before ?

A database with empty cells which actually refer to the last “non-empty” cell above.

Fill 1

This is not very handy, especially if you want to do statistics (split per region is this case).

For example, graphs, sumif function or pivottables/charts all require each row to be filled-in. A better database for accurate stats would then have to be :

fill3

You will find below a tip to perform the data cleansing required:

fill 2

Note that I am using the “Go To” command (Press F5) to select all the blank cells in my range.

You can now do your stats !!


Did you like it ? Please share the article, but also your Excel & VBA tips below in the comments section.

Many more to come…

 

Advertisements

2 thoughts on “Tip of the month (3)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s