Home » Analyse

Tag: Analyse

person sitting on chair holding iPad

Insert subtotal rows into sorted data

Excel Sort Levels

Learn how to insert subtotal rows into sorted data without having to spend time doing it manually.

Recently I ran a training session for an Accounts Manager and her staff.

They spent a lot of time pulling data out of their in-house computer system, sorting it by customer and then inserting a new row at every change in the customer name.

This was so that they could place a SUM into the row to total what the customer had spent with them.

Using the Subtotals feature I showed them a quick and effective way in which to summarise their data. And yep! They were pretty impressed! Continue Reading

MacBook Pro, white ceramic mug,and black smartphone on table

Sort an Excel list into numerical, date or alphabetical order

MacBook Pro, white ceramic mug,and black smartphone on table

Sort an Excel list into numerical, date or alphabetical order to organise your data into a more useful arrangement.

Once you know how to use the Sort command you can organise information so that it’s easier to interpret. For example, if you receive a list of purchases made by many different clients on different days of the month it may be easier to see which clients are buying from you at different times of the month. By sorting the data by client and by date you can easily analyse who is buying and when.

Continue Reading
person using black laptop computer

Sum only visible rows when Filtering data in Excel

A common question I get asked when I’m teaching people how to filter data is “how do I sum only the visible rows when filtering data in Excel”?

It’s very common for people to try creating a SUM total at the bottom of a list and then filter the data hoping that the total will change to reflect the SUM of the data displayed. However, the total doesn’t update. It still shows the SUM of the entire list.

You can definitely use the AutoSum button to instantly create a total that will update as you filter your data. Here’s how you do it.

Continue Reading
man holding his chin facing laptop computer

5 Reasons why your Excel filter may not be working

Are you are having a few hassles when filtering?

Is the filter not working properly or as you would like it to?

Here are some reasons why your Excel filter may not be working.

Excel has an expectation that you have prepared your data to meet some basic layout standards before you use filter. Get these right and you will minimize filtering hassles.

Check you are following these Filtering rules...

person using MacBook pro

Filter data in Excel

Learning how to filter data in Excel enables you to find and work with just a subset of a larger list or database.

Filtering displays only the rows that contain a certain value or that meet a set criteria, while hiding other rows that don’t. Once you have filtered your data you can then work with just the visible set of data.

For example you might like to filter a list to find all sales made to one customer and then print only that data. Filter will allow you to do this in just a few clicks. Continue Reading