HomeHome SearchSearch MenuMenu Our productsOur products

How I helped this administrator clean up her sales reports in minutes

by , 24 June 2014
During a recent Excel training course I presented to finance professionals, a delegate asked how he can help his administrator clean up their databases.
At the moment, she spends up to six hours every month cleaning the sales reports before she's able to AutoFilter! Keep reading to find out how to do this in minutes...

**********Recommended product**********
Are you an accountant or bookkeeper? Don't let an Excel problem interrupt your workflow

Over 5 920 Excel questions are searched on Google in South Africa every month...
If you're adding to that statistic, you'll know how hard it is to find a solution that specifically relates to your Excel problem.

But not anymore!
Get Excel guru, Adrian Miric to solve it in the next five minutes.
Two vital steps before you start formatting your spreadsheet

Before you start making any changes to your spreadsheets, make sure you follow these two rules:1.    Make a copy before you work on the data. You don't want to realise you made a mistake but don't have an original.
2.    Determine a control total to check against as you work through it. Whenever you delete rows you run the risk of deleting an important row and won't know unless you compare it to a control total.
We're only interested in the data in the red block below and want to quickly get rid of the other rows (and keep in mind that this pattern repeats itself over the next 300 pages of the report).

Keep reading to learn how to clean up your spreadsheets before you format them.
**********Recommended product**********
How to use the AutoFilter tool

Instead of going through the entire spreadsheet and deleting the unnecessary data, you can use the AutoFilter tool. Select the area you want to format, and click the Data tab and the click the AutoFilter button.

The logic is that you filter out the data you don't want. So you select all the rows you want to delete.
At the end of the formatting, I only want to display sales people's codes. So I select all the data that I don't want – and unselect sales peoples' codes.
When you click OK you'll see all the rows that you want to get rid of as shown below.

Now highlight the rows you want to get rid of and click Delete Row. To highlight the whole row, click on the row numbers as opposed to highlighting the cells (click blue numbers on left hand side).

Now select Data - > Filter -> Clear

You'll see that in one step you've removed all your unwanted rows. Now you only display the content you want to display.  

And that's how I helped this administrator clean up her data in minutes. Use this tip to clean up any of your Excel databases. And keep an eye out. I'll send you a bulletin every month to give you tips and tricks to make working in Excel much easier. I also run Excel seminars specifically geared towards helping finance professionals use Excel efficiently. Email seminars@fsp.co.za to find out what's currently running.

Until next time,

Adrian Miric
Excel Expert

P.S: Want to learn tips and tricks to halve the time it takes you to do basic finance tasks? Join me at the Excel for Beginners Workshop in July. Email seminars@fsp.co.za to get your name on the hotlist and be the first to receive confirmation that bookings have opened.

Related articles

Related articles

Watch And Learn

Related Products


Recommended for You 

  Quick Tax Solutions for Busy Taxpayers – 35 tax answers at a glance

Here are all the most interesting, thought-provoking and common tax questions
asked by our subscribers over the last tax year – everything from A to Z!

To download Quick Tax Solutions for Busy Taxpayers – 35 tax answers at a glance click here now >>>
  Employees always sick? How to stop it today

Make sure you develop a leave policy to regulate sick leave in your company.

BONUS! You'll find an example of the leave policy and procedure in this report.

To download Employees always sick? How to stop it today click here now >>>
  Absenteeism: Little known ways to reduce absenteeism

This FREE e-report will tell you how you can reduce absenteeism in your workplace while avoiding the CCMA and without infringing your employees' labour rights.

To download Absenteeism: Little known ways to reduce absenteeism click here now >>>
  7 Health & safety strategies to save you thousands

Don't let a health and safety incident cost you one more cent. Implement these seven
strategies in your company today.

To download 7 Health & safety strategies to save you thousands click here now >>>