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 

How to add a percentage increase to a monthly target in Excel

In this example, I am looking at how to add a percentage increase to calculate a monthly projected fundraising target.

Imagine my team is fundraising for a new community centre. In the last month they raised £76,000. I’d like to increase the total per month, but not put them off, so I want to add 4.5% increase to each month.  So November’s target is 4.5% greater than October’s target, and December’s target is 4.5% greater than November’s figure.

Excel can calculate this all for me.  So I set up a worksheet with the months of the year, and then a column for the current month’s fundraising target as our starting point.  The total is £76,000 and I want the Target Fundraising column for November to show the total as £76,000 plus the 4.5% included. So my formula is =B2*104.5%.

(NB. If I used =B2*4.5% it would show the result as 4.5% of the total.  By adding 100 to the 4.5% the answer will show the sales target including the 4.5%).

When I then copy the formula down the remainder of the column, the formula will take the total figure from the cell above it and apply 4.5%.

percentage-to-total-excel-visual-basic-training

I can see how the formula works by using Formula tab, Formula Auditing, Show Formulas.

NB This is useful if the % increase per month is the same.  If there are changes in % we need to add absolute references…but more on that in another post.

Our courses cover everything from Excel introduction, Intermediate to PowerPivot and Excel Visual Basic Training. Find out more on http://www.microsofttraining.net/excel-vba-training.php.

 

How to calculate a percentage reduction in Excel using formulas

One of the many things I like about Excel is that I can set up the formula in one cell and copy it to other cells.

If I want to apply a percentage reduction to a figure, I can set it up for the first line of data and then autofill the relevant cells.  With adverts already reminding us to spend for Christmas and plan for the sales, here is a simple example of applying a percentage price reduction in Excel.

Applying the same percentage discount to multiple items
A shop wants to calculate discounts of 33% for all sale items in the January sale, and an Excel sheet can set these up easily.

All the items will have a 33% reduction applied on 1st January 2013.

apply-percent-excel-courses-london

I create a formula using the data in B2 and then copy the formula to the remaining cells in the column

To set up the formula in the first cell, the price in B2 is selected, and then multiplied by 33%.  The formula will appear as =B2*33%.  By clicking enter the discount is applied.  This formula can now be copied down the column.

fill-percent-excel-courses-london

Use the autofill handle to copy the formula in the remaining cells.

If the shop now wants to work out the new price, they can add an extra column and take the sale reduction from the original price using a formula.

new-price-column-excel-courses-training

Simple formula applied to calculate the new price.

In Column D, the calculation is the original price in column B2 minus the sale reduction in C2.  So the formula is =B2-C2.  This formula is then autofilled down the remaining cells and the new price is updated.

This works well, when you have a same percentage discount applied to a group of items.

For more information on Excel courses London vlookup pivot tables and more, take a look at http://www.microsofttraining.net/excel-vba-2007.php

Formatting cells in Excel with currency

When I add financial data to my worksheets, I generally type in the content and format the cells afterwards – here are two ways to format cells with currency.

I select my active cells, then I then click on all the cells, right click on format cells, and then go through the dialogue box to select “currency”, then 2 decimal places….I then use the fill handle to copy the format into the remaining column.

format-currency-excel-vba-courses-london

Formatting cells using the Format Cells dialogue box

This works as a process, but I can make this quicker using the currency button in the Home tab.  I select the cells, then go to the Home tab, Number section, and select the currency button.

Home-tab-currency-excel-vba-london

Currency button adds the format in one click. Nice.

I achieve the same result but in a quicker way.

For syllabus information on our courses from introduction to Excel VBA Courses London http://www.microsofttraining.net/excel-vba-2010-training-course.php

What is Excel VBA and why should I learn it?

A common theme for me in Excel training is the comment “I didn’t know Excel could do that” – and training is a good way of finding out what Excel can do (beyond what we are currently using in our work environments).

Excel VBA has been a bit of a mystery to me, and without knowing what it is, why should I add that to the list of Excel skills I want to acquire?  Here is a really basic guide to Excel VBA.

What does VBA stand for?
VBA stands for Visual Basic for Applications.

What is Excel VBA for?
VBA is a language used for programming tasks and actions in Excel or developing additional functions in Excel worksheets that are customised to your work needs.

VBA is great for automating actions – saving time by performing programmed actions time and time again.  So you don’t have to set things up from scratch each time.

For example, if you are at an intermediate level, you can use macros without opting for the use of VBA.  However, understanding and using VBA gives you more flexibility to set up Excel to act on commands that may be too complex to be recorded in a macro. In this situation, VBA is suited to more advanced users of Excel.

Uses for VBA in the work environment 
For those users who can apply VBA, it can make life easier for us who are still developing our skills.

Automating procedures – for example, a monthly summary report can be set up using VBA – so you can press a button and the report is generated (conducting all the different actions so you don’t have to).

Speeding up work by using VBA macro to automate repetitive actions - if you find yourself completing the same action over a number of Excel workbooks, consider that  VBA could save you lots of time, by automating the action.

Customise Excel so that anyone in the organisation can use it.  If, like me, you aren’t using VBA yet, it is a breath of fresh air if someone can sets up commands and functions so I can just get on with my work.  So no matter whether the staff member is using Excel everyday, or infrequently, VBA is key to speeding up work and cutting out inaccuracies for everyone in the organisation.

For more information on our range of Excel VBA Training, please see http://www.microsofttraining.net/excel-vba-2010-training-course.php.

 

 

Understanding the different types of commands on the Ribbon

When I made the switch from Excel 2003 to Excel 2010, the Ribbon confused me and  it still takes me a while to figure out which tab holds the functions I need.  When I was on the intermediate Excel course the other week, I did notice that the trainer was talking about the buttons in different ways.

I felt it was time to devote a bit of time to learn about how to use the Ribbon more effectively including understanding the different types of commands.

ribbon-excel-vba-training

The Ribbon in its full glory.

Minimising the Ribbon I kept losing the Ribbon this week.  Didn’t know why, but it seemed to minimise on a whim.  That was until I learned that if I double-clicked on any tab, the Ribbon minimised.  To get it back, I needed to double-click on any tab again.   It is helpful to minimise the Ribbon when you want to see more rows (you can gain 5 more rows in the view if you minimise (not so helpful when you do this by accident and can’t work out what you did!)

Tip:  the shortcut to minimise the ribbon or bring it back to its full size, use Control and F1.

What are the types of commands on the Ribbon?  There are six main categories for commands: one-click, toggle, split buttons, drop-down and tick box.  Categories can be mixed so it is useful to understand the basics to develop your Excel skills.

The does-what-it-says-one-click button. These perform a simple action or function when you click on it. For example, clicking on the text alignment button applies the format to the active cell.  Other simple buttons in formatting include the Italic button, or bold, or increase or decrease font size buttons.

alignment-button-excel-vba-training

Click on the alignment button to format a cell in one click

Toggle buttons, now this is pretty neat, and hadn’t spotted this until someone pointed it out to me.  Excel tells me when I’ve applied this button to a cell, by changing the colour of the button, so I can toggle between applying and deselecting. This is incredibly helpful when I have different formats applied to cells and I need a reminder, or want to deselect the format quickly.

toggle-excel-vba-training

In this example, my category headings have bold and bottom alignment applied, and the buttons are yellow to show me that.

Drop down buttons have little arrows next to the button, reminding me that there are more options available.  A good example is the Borders button, which offers me different borders to apply to the cells I have selected.  Colour fill is another example, where I can click on the down arrow, select a colour and it is applied.

simple-button-borders-excel-vba-training

Drop down button options are displayed for me to select.

Split buttons are a hybrid of drop down buttons and one-click wonders.  So you can click on the button itself and the action is applied.  Click on the down arrow next to it, and more options are available and you can apply them from there.  You still have the benefit of the button changing colour so you know when an action is applied.

Tick box buttons are on/off kind of buttons.   A good example is in the View tab, where I can tick when I want to view gridlines, or show headings.  Quick to apply or deselect.

view-tab-tick-box-excel-vba-training

Tick box options in the View tab, so I can tick or untick actions such as show grid lines, or headings.

If you can’t remember what a button does, hold your mouse over the icon, and Excel will give the description.  If you use a button frequently, right click on it and add it to your Quick Access Toolbar, to save you from locating it on the ribbon each time.

The more familiar you get with the Ribbon, and the styles of buttons, the faster it will be to apply actions and functions.  By understanding the Ribbon better, I can also speed up my learning on courses – knowing the terms and locations makes it easier to follow exercises.  So I am focusing on understanding the commands and applying them so that I can move up to Excel VBA training soon.  For more background on the different skills and levels offered, have a look at http://www.microsofttraining.net/excel-training-london.php

 

 

 

 

 

 

 

 

 

 

 

 

What can I use Excel for?

This may seem a back-to-basics question, but it a useful one, at any stage of Excel.  This is something I’ve learned from attending our courses – it is always good to ask “What else can I do with Excel?”, “Can I automate that function?”, and “Can Excel do that?”

Excel is a spreadsheet and a versatile one at that.  It is fantastic for number-crunching and great for managing text data.  Excel functions such as sorting, filtering, charts and formulas allow you to analyse text and numerical data.  You can save time by automating functions such as calculations or conditional formatting, such as highlighting overdue invoices.

What can you use Excel for?  Well, everything from lists to charting progress and financial modelling.  Take a look at the list below and see if you are using Excel to the max.

Organising information with Excel
I know I can use Word to create a list but with Excel I have more scope to organise lists, categorise information, sort and filter lists, and set deadlines for items.  I use Excel for lists for work, home and even for study.

Excel for time management of work. I have an Excel sheet with the daily core tasks and project-based work.  This allows me to allocate time for each item, over the day, week, month and year.  I have the flexibility to manage tasks based on their priority, change time allocated and immediately see the impact of changing or adding tasks.  The lists includes text plus working with dates and currency (for costing up projects).

Excel for learning vocabulary in another language.  I use Excel to learn my French vocabulary.  I can colour code words by gender (using conditional formatting), add categories so I can filter lists by topic area.  I can even test myself by hiding words, typing in the word and Excel has a formula to say “correct” or “incorrect”.

Excel for Charts
Charts are useful for work and home.  Excel has ready-made tools so that I can create charts within a few clicks.  This is great for visual presentations of sales data, changes in customer demand, and income and expenditure figures by month or quarter.

Charts for monitoring sales performance.  Simple to set up, Excel can automatically update your chart to identify trends, highlight categories.  You can even embed these in your Powerpoint presentations – when you update your Excel sheet, the Powerpoint presentation is automatically up to date.

Charts for tracking weight and fitness measures.  You can customise an Excel worksheet so you can manage your health and fitness.  When you update your data, such as weight, time trials for stamina, or number of repetitions with weights, Excel charts can instantly show you the trends.  This can help you to tailor your training, by identifying potential over-training, spot trends in reduced performance and revise goals accordingly.

Number crunching
From Inland Revenue tax returns, to working out project costs, income and expenditure patterns, and client records, number-crunching is Excel’s strong point.  Never one for mental arithmetic, I can set up formulas in Excel so it automatically updates when I add new figures.  With formulas ready-made for me to customise, I can analyse data more quickly…so my focus is on outcomes, not on data entry.  Number-crunching is more accurate with Excel, with error and formula checking available with a click.  With Excel VBA you can set up and develop functions, debug codes and control program execution.  Number crunching covers everything from introductory level to advanced VBA.

Creating dashboards in Excel 
I have Excel workbooks with many worksheets and creating a dashboard in Excel allows me to have a summary of all the key data in one sheet in a clear format.  I can set it up to automatically update with figures from other worksheets, or even other workbooks.  Fantastic for sharing data with colleagues, dashboards provide an immediate overview so that discussions are based on accurate and current data.

Excel for creating diagrams and adding graphics  
Maybe this isn’t the most common use of Excel but diagrams and graphics in Excel can give your work the wow factor.  It is a great way to tailor information to suit your audience, for example, those who need more visual representations of numbers through diagrams or charts.  If your audience includes everyone from HR to Finance, your Excel worksheet can include graphics to show project staffing to assessing the return on investment. I can even add a screenshot if I want…Excel really is that versatile.

Graphic items can add a professional edge to your workbook and enhance the content in your worksheets.  SmartArt graphics such as organisational charts or process diagrams can complete an Excel project proposal, with the financial data linked to the project processes shown in a diagram next to the figures.  Remember, graphics can include your organisation’s branding – a quick way to customise your documents.

Save time by automating complicated tasks in Excel 
Possibly my favourite thing about Excel.  Once I have figured out that I can automate a task, Excel can do the leg work for me.  Automating tasks can include using macros, using formulas, or conditional formats.  For example, I can update my time sheet  and Excel will automatically update the figures for my invoice, or project monitoring worksheet.  With 3D formulas, I can link calculations across my workbook or even other Excel worksheets – cutting out the need to update several documents with the same data.

It doesn’t matter if you are new to Excel or an experienced user, you can always get more out of the program by asking questions, experimenting with formulas and developing more complex skills using VBA Excel or PowerPivot.  Take a peek at what you can learn at http://www.microsofttraining.net/excel-vba-2010-training-course.php.

 

 

 

 

What are contextual tabs in Excel?

I was on an Excel Intermediate course last week, and I noticed that when I was working with a chart, specific tools appeared.  When I clicked out of my chart, the tools would disappear…What is going on Excel?  Why are you doing that?  How do I get them back?  (Mild panic followed…”have I broken it?”)

Fortunately, my calm and understanding trainer explained that these disappearing tools are called contextual tabs.  And no, Excel doesn’t hide them to induce Excel-panic, it is actually being pretty smart.

Contextual tabs appear when you are creating or editing an object like a chart, or table or even a diagram in Excel.  The contextual tabs give you the options and tools you need to work with these objects and then when you click out of the object the tools are tidied away.  To get them back, you click on the object, and the tab appears again.  It keeps your working space uncluttered.

contextual-tab-visual-basic-excel-training

The green tab is a contextual tab, that appears when I’m working with my chart.

In the above screenshot, I’ve put a chart into my document, and you can see a green highlighted tab – this is the contextual tab which I can use to amend my chart.  When I click out of my chart, and back into my spreadsheet, the contextual tab disappears.

no-contextual-tab-visual-basic-excel-training

When I click out of my chart, the contextual tab disappears…look! the green highlighted tab isn’t there…

See if you can spot the difference!

Getting used to what Excel does, and when, is really essential – I can get more out of it if I understand why it does these strange things. Contextual tabs stop winding me up when I know why they appear, and why they don’t.

When I’m ready, after a bit more practice, I’m going to try visual basic Excel training – but for the next few weeks, I’m going to keep experimenting with what I’ve learned so far.  If you want to lose the Excel-panic or just get more out of the program, take a look at what the courses cover on http://www.microsofttraining.net/excel-vba-2010-training-course.php.