Home » Archives for Sharyn Baines » Page 2

Author: Sharyn Baines

people sitting down near table with assorted laptop computers

Find, modify and break links to an Excel workbook

people sitting down near table with assorted laptop computers

 

Learn how to find, modify and break links to an Excel workbook.

Linking is great, until you no longer need it. Then it can be a bit of a pain.

A couple of challenges may come your way if you weren’t the creator of the workbook that contains links. One challenge is identifying where the linked data is within your workbook and another is actually breaking the link. Continue Reading

person writing on white notebook

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

person writing on white notebook

 

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. Continue Reading

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
Sharyn Baines

Freezing column and row labels in Excel

Sharyn Baines

Freezing 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. Continue Reading

woman using desktop computer

Move, Copy and Paste information in Excel

woman using desktop computer

 

Learn how to move, copy and paste information in Excel. Once you have mastered Cut, Copy and Paste you will find yourself shifting data around easily and reusing copied data, saving you the need to recreate the same information again.

All Windows applications have access to the Windows Clipboard. This is a special area used for moving and copying information.

The ‘Cut’ command moves a selection to the Clipboard while the ‘Copy’ command copies a selection to the Clipboard.

Continue Reading