Home » Our Blog » Excel Time Saving Features

Category: Excel Time Saving Features

The Excel Time Saving Features category groups together posts that cover the Excel features that save you time. These posts are primarily for those who spend many hours formatting and updating data in worksheets. If you use Excel often but find you seem to be spending way too much time getting the results you require, this category is a good place to hang out.

Use Conditional Formatting to identify cells that are of interest

sharyn baines

Learn how to use Conditional Formatting to identify cells that are of interest.

For example, you can apply a Conditional Format that checks to see a cell’s value is greater than $500. If it is the Conditional Format can change the Fill colour of the cell. Therefore making it really easy for you (and others using the file) to quickly identify any cell with a value greater than $500.  Read more

Use the SUMIF function to total only the cells that match your requirement

sharyn baines

Use the SUMIF function to total only the cells that match your requirement.

For example, if you wanted to know the total sales made by one of your sales team members you can use the SUMIF function to only add to the total the sales made by a certain member. Gold! This function saves you a lot of time.

Prior to learning this function most people filter their data based on their requirement, e.g. the team member’s name, and then copy and paste the info into another worksheet. Once in the new worksheet they then use the SUM function to create the total. Read more

Insert subtotal rows into sorted data

sharyn bainesLearn 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! Read more

Sort an Excel list into numerical, date or alphabetical order

sharyn baines 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.

Read more

Freezing column and row labels in Excel

sharyn bainesFreezing column and row labels in Excel (a.k.a Freeze Panes) locks parts of your screen so that you don’t lose sight of the heading labels at the top of a column or beginning of a row when you scroll.

When working on large worksheets, labels used to identify data can be lost when you scroll in either direction, and then you find yourself wasting time scrolling backwards and forwards, up and down, just to check you are actually in the correct column and row. Read more

Sum only visible rows when Filtering data in Excel

sharyn bainesA 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.

Read more