Author Archives: Best STL

What is a pivot table?

It is possible to use Excel for data entry and miss out some of the tools that can make analysing and summarising data easy-peasy. For me, this was Pivot tables.  I avoided Pivot tables because a) I didn’t know what they were and  b) I thought it would be too difficult to use as they sounded complicated.

My solution needed a calm and experienced trainer and an Excel intermediate course to see the benefits of the Pivot table, with ready-made data to practise on (so I couldn’t break anything).  Here is what I’ve learnt….

A Pivot table is a tool used for creating reports that summarise the data in your database.  They can be created in a few mouse clicks (yes, really they can…).

Why is is called a Pivot table?
The pivot part refers to the way you can use a table to analyse data from different angles and perspectives.  The pivot part means you can move around your information, manipulate it, rotate it, group items, turn the data around to present it in the most digestible format for your needs.

Pivot tables are dynamic and interactive.
With Pivot tables you can create summaries with whatever data you want to focus on, update them with new data by refreshing the table, and even add formulas to help you make new calculations.

Pivot tables are flexible.
This means that you have the flexibility to choose what you want to summarise and how you want to show it (by frequency, by month, by team, by customer, by account etc).

Pivot tables are easy to update.
This means when you enter or amend data into your database source, you can update your pivot table with the new figures by refreshing the content.

You can customise they way they look.
You can use the Pivot table to create professional reports and customise them to meet your company branding.

Why are Pivot Tables useful?

They Analyse data to meet your needs with a visual summary.
It is much easier to analyse data in a large data sheet if it can be broken down into usable chunks of information, that are easy to read and understand.

For example, if you wanted to analyse expenditure across five departments to identify trends, spot good practise (who is keeping overheads low) and see if there could be budget savings, or map expenditure to sales by month to identify gaps.

You can use Pivot tables to answer specific questions about your data.  
You have the flexibility to find out answers from your data.  Anything you are keeping in your database can be queried.  Such as: Do you want to know which month of the year has the most staff requests for annual leave?  Who is the member of staff generating the most sales?  Are there peaks of staff illness in the last year indicating a problem which could be tackled by addressing office environment?

Now you have the definition and basics of why pivot tables are useful, you are closer to getting more out of your data.  Take the plunge and explore with an Excel intermediate course.

 

 

Spellcheck in Excel and other proofing tools in the Reviews tab

For that professional edge it is really worth using Excel’s proofing tools to make sure that your worksheets are accurate.

With more teams sharing Excel documents, adding comments and making changes, you need effective ways to track changes, check for duplicates and ensure that your business works with the most up to date and accurate information.

All the tools you need, to proof your documents, are located in the Review tab. You can even use the tools in this Ribbon as your proofreading checklist.

When you are upgrading from an older version of Excel, or building your skills, the Review tab is often overlooked.  Take a trip along this Ribbon and look at how it can help you get more out of your data and shared working.

proofing-financial-modelling-courses-excel

The Review tab in Excel has all the tools you need to ensure the accuracy of your worksheet.

Spellcheck
Spellcheck is common in Word and even in Outlook, but in Excel it is often underused. Nothing can annoy clients more than names spelled incorrectly, but with a quick click, your worksheet can be checked and corrected.

Research
Excel won’t do your work research for you, but it will take you to places that can help you research the details such as dictionaries, reference materials and even translation. Click on the Research button and a side panel appears with the reference materials ready to go.

Thesaurus
This may not be top of your list, but this is really useful if you over-use particular words and want to broaden the vocabulary – users can be distracted by the constant repetition of terms, so use this button to help you be, well, more cosmopolitan and interesting…

Translation
With international clients, translation tools are really helpful.  This can save flipping through huge hard copy dictionaries, and type the word you need and access the translation.

Working with Comments
I use comments often in my Excel worksheets.  I can store extra notes here and not clog up my rows and columns.  This is also where colleagues can add notes when we are sharing the same worksheet.  To help you with this, there is a whole section devoted to Comments.

comments-tab-financial-modelling-courses

Comments section in the Review tab give you access to great tools

Not only can you add comments as you need to, but when you are looking at the whole worksheet and want to review all the comments quickly, you can click on the “Show All Comments”.  And when it comes to printing your document, you have the option to hide the comments, or include them at the end.

Changes – making them and protecting your worksheets from unwanted ones!
If you have spent hours setting up complex formulas and linking worksheets, the last thing you need is a well-meaning colleague changing them.

changes-tab-financial-modelling-courses

Changes tab includes recording changes, and protecting your document from unwanted ones.

In the Changes tab, you can protect the sheet, or entire Workbook.  If you are sharing your workbook, using these options can save lots of time.

Whether you are using Excel to plan a home project or using it for financial modelling courses in Excel can help you to get more out of the program.  Review what we can do to help you with http://www.microsofttraining.net/excel-training-london.php

 

 

 

 

 

Using SumIF to add up specified values in Excel

Part of the process of learning about Excel is overcoming the fear of new terminology, especially when it is linked to functions. Today’s term is SumIF.  I use Autosum all the time, so why would I need to use SumIF?

Well, Autosum adds up every cell that I select in a row or column. Great for totalling invoice totals, or expenditure for a month, or adding up items in inventory lists.

But what if I only want to add up items over a specific value?  For example, if I have the authority to make payments up to the value of £250 within a list of expenditure – could I add up the items that were up to and including £250 in value?

I can do this if I use SumIF – as it is designed to add up a range of cells only when the criteria I specify is met.

The format I need to use is =SUMIF(range, criteria)

In my example, the SumIF formula I will use is =SUMIF(B2:B8,”<=250″), as my range is B2 to B8, and I want to see invoices authorised from less than or equal to the value of £250).

SumIF-visual-basic-excel-training

The formula bar shows the SUMIF formula, with the range and criteria specified. The result is shown in cell E2.

This is a simple example where SumIF can calculate the totals authorised by the admin department and those authorised by the Manager.

SumIF can do far more than this, and it is worth exploring the options it gives you.

There are lots of uses of the SUMIF function that can really transform your Excel worksheets.    From Introduction to Advanced, PowerPivot to Visual Basic Excel Training, our courses cover the range skills that can boost your performance at work.
Take a look at what Excel can do for you at http://www.microsofttraining.net/excel-training-london.php