Microsoft Office Tutorials – Paul Brown Training Consultants Blog

How to User Slicers in Microsoft Excel 2010

April 10, 2011 Paul Latest Training News, Microsoft Office Tutorials

Overview
Slicers are the new feature in Excel 2010 which has got everyone talking. Essentially Slicers are a user-friendly filtering system which makes PivotTables & PivotCharts easier to work with and now brings a high level of dashboard capability to Excel.

Microsoft Excel Slicers Dashboard.

Microsoft Excel PivotChart. First Create Your PivotTables
The first step in using Slicers is to create your PivotTables & PivotCharts:

Select the Insert tab on the Ribbon then click the PivotTable button and choose PivotChart.
Next select the data range and choose to create the PivotTable and PivotChart on a new worksheet (once we have created a dashboard we can hide the PivotTable tabs)
Now choose the required fields in the Field List to create your PivotTable & Chart.
Finally cut and past the PivotChart into a new worksheet. This will form the basis of our ‘Dashboard’.
Repeat the above process as necessary to create further PivotCharts and place them on the dashboard.

Each of the PivotCharts on the dashboard can be individually filtered directly using the Field Buttons. However, the slicers we create next will take over this function and provide a higher level of interactivity between PivotCharts. We can therefore remove the Field Buttons, here’s how:

Click inside the PivotChart and the PivotChart Tools group will appear on the Ribbon.
Next select the Analyze tab and click the Field Buttons menu.
Choose Hide All to remove the Field Buttons on the PivotChart.

create slicer. Creating the Slicer
The next step is to create slicers to add real functionality to the dashboard:

First click inside a PivotChart and then select the Insert Slicer button menu. Choose Insert Slicer from drop-down the menu.
You will be presented with a dialogue box containing a list of PivotTable fields.
Choose appropriate fields to wish to filter then click OK.

How to Use Slicers
The slicer will be created as a box with a number of buttons which enabling filtering. Clicking an individual button will change the PivotChart accordingly. Multiple selections can be made by holding down the CTRL key. The filter can be removed by either clicking the funnel icon or alternatively dragging across all the buttons to select them all.

pivottable connections Enhancing Slicer Functionality
A major advantage of using Slicers is the ability to filter multiple PivotCharts simultaneously, creating true dashboard functionality. Here’s how to link PivotCharts together:

First select a Slicer and right-click in the header area (where the Slicer title appears), then choose PivotTable Connections from the menu. Alternatively you can select the Options tab on the Ribbon and choose PivotTable Connections.
Next select from available PivotTables those that you wish to connect to the Slicer.
Finally click OK to complete the process.

Creating a Dashboard
We now have the basis for a full dashboard system which enables simultaneous filtering of multiple data fields. Simply set up all your PivotCharts on one spreadsheet tab and create Slicers with multiple connections as necessary. Group your Slicers with the PivotCharts and you will have a user-friendly data filtering system. You may find it advantageous to colour code your charts and slicers to match, so that you can filter data more easily:

To change you PivotTable colour, first select the Design tab in the PivotChart tools group.
You can now select from a range of chart colours in the PivotChart styles gallery.
To change Slicer colours, first click a slicer then select the Options / Slicer Tools tab on the Ribbon.
A corresponding colour can now be selected from the Slicer Styles gallery.

Summary
Slicers represent a powerful new filtering tool in Excel 2010 which appears set to transform the way that we use PivotTables and PivotCharts.

Microsoft Excel 2010 Sparklines Tutorial

March 26, 2011 Paul Microsoft Office Tutorials

Sparklines are one of a number of features which are new to Excel 2010. Essentially they are small single-cell graphs and represent a great new way to visually interpret data. In the following tutorial we will take a simple step-by-step approach to creating a sparkline:

Creating Sparklines

First download and open the spreadsheet Clothing Sales.
Next activate the Insert tab on the Ribbon and click the Line tool in the Sparklines group.
Now, with the cursor in the Data Range field, click and drag to select the cell range B6:E6.
Next tab to the Location Range Field and click in cell F6.
Now click OK and the Sparkline will be inserted into cell F6.
To change between Line, Column & Win / Loss graph types, just click the appropriate tool in the Type group.

Microsoft Excel Training Courses.

Sparklines can be placed anywhere within the spreadsheet, and do not have to be on the same row or in the same column. In this way, Sparklines can be grouped together to show changes in a wide range of data at a glance

Copying Sparklines
Sparklines behave like any other formula in that they can be copied down a column or across a row to chart multiple data ranges. Just create your first Sparkline, then click the AutoFill handle (the small black square situated in the bottom right hand corner of each cell) and drag down a column or across a row. Additional Sparklines will automatically be created which will represent data ranges in relevant rows or columns.

Customising Sparklines
The default setting tends to produce Sparklines which can be somewhat lacking in detail and clarity. The Show group of tools contains a number of options which produce highlight points on the Sparkline graph:

First Point
Last Point
High Point
Low Point
Negative Points

The Style gallery contains a number of pre-set designs for each Sparkline type. Sparklines can also be customised using the Sparkline Colour & Marker Colour options.

As a final note, Sparklines are by definition small, which can sometimes be a problem. It’s worth remembering therefore that they can be made larger and thereby easier to view by increasing both column width and row depth.

Using the IFERROR Function in Excel 2007

November 11, 2010 Paul Microsoft Office Tutorials

The IFERROR is a simple but useful function in Excel 2007 and simplifies the old ISERROR function from earlier versions. Essentially, IFERROR provides an easy means to replacing standard Excel error messages with something more ‘user friendly’. Many Excel users will be familiar with errors such as #DIV/O!, but to others these messages can seem ominous and confusing. Using IFERROR we can customise the output message based upon a particular error and present it in a manner that any inexperienced user will be able to understand.
using IFERROR in Microsoft Excel 2007

First download the Excel 2007 spreadsheet Using IFERROR.
You can see that in column D we have a couple of #DIV/O! errors where the unit price has been entered as zero. Dividing any value by zero will produce this standard error message.
Now click in cell D4 then select the Formulas tab.
Click the Logical button and select IFERROR to launch the function wizard.
In the Value field we need to enter the formula B4/C4 to calculate the number of units sold.
Next in the Value if Error field, type a text message which will be displayed in place of the normal #DIV/O! Make this something self-explanatory such as “Check Price”.
Now click OK and finally copy the function down column D.

This is one of the simplest of all Excel functions, but it can make it much easier for a less-skilled user to work with your spreadsheets.

We cover this and many other time-saving topics on our Microsoft Excel 1 day courses. We specialise in on-site training and can deliver Foundation, Intermediate and Advanced levels of Excel at your own offices anywhere in the UK. For more information email info@pbatraining.co.uk or call free on 0800 2922842.

How to Correctly Define & Link Tasks in Microsoft Project

November 7, 2010 Paul Microsoft Office Tutorials

Microsoft Project has been around for some time now and is considered by many to represent the industry standard scheduling tool. In the right hands it can certainly make the task easier and save both time and money by highlighting possible scheduling problems.

Don’t Get into Bad Habits!
However, our experience of delivering MS Project courses from Foundation to Advanced levels has shown that many people who are self taught frequently mis-use the application through no real fault of their own. It is simply that Microsoft Project isn’t the easiest of programs to understand without guidance and the interface can lead the unwary novice into forming ‘bad habits’ which can seriously undermine the true potential of the application. It’s the old adage that “we don’t know what we don’t know!”

The Most Common Problem
Without doubt, the most common ‘bad habit’ that I see amongst self-taught users is in respect of defining tasks. It is often assumed that because the Task Sheet has columns for start and finish dates, these should be defined when a task is entered. It would seem a logical thing to do and certainly looks as if Microsoft Project requires the information. In fact it is in most cases exactly the wrong thing to do, as setting a task finish date will automatically apply a ‘Finish No Earlier Than’ constraint. Constraints come in 3 forms: Flexible, Semi-Flexible and In-Flexible. A ‘Finish No Earlier Than constraint is semi-flexible which removes some of Microsoft Project’s ability to re-schedule and generate alternative scenarios. What we should in fact be doing, is setting only the duration field and initially allowing all tasks to commence on the project start date. When the tasks are later linked together, the start and finish date will automatically take care of themselves. The big difference now is that all tasks have a start ‘As Soon As Possible’ constraint, which is the most flexible option. Here’s how:

Entering Project Tasks
Entering Tasks in Microsoft Project

First click in the Task Name field on the Task Sheet and enter an appropriate name.
Next click the Tab key and enter an estimated Duration. Use either the spinner buttons, or simply type the appropriate number of days into the field.

Notice how each task has the project date as it’s start date and the finish date is automatically determined by the task duration. You will also see that the Gantt chart starts to build on the right-hand side. The next step is to link the tasks together:

Linking Project Tasks
Linking Tasks in Microsoft Project

First double-click on second task in your list and the Task Information window will open.
Next click the Predecessors tab, then select the ID field and type a 1. Click Enter.
You will see that MS Project automatically enters the Task name and also sets a linkage type of Finish-to-Start. This can be edited to reflect each of 4 different task relationships: Finish-to-Start, Start-to-Start, Finish-to-Finish, Start-to-Finish.
Finally click OK to Exit the task Information window and repeat for each task. Notice how the Gantt chart updates with each new task relationship.

Further Information
Clearly there is much more to discuss in respect of task relationships and linkage types, but I hope this short tutorial will at least get you started on the right track to using Microsoft Project. For more information about our 1 day Microsoft Project training courses you can email info@pbatraining.co.uk or call us free on 0800 2922842.

How to Use Goal Seek in Microsoft Excel 2007

May 29, 2010 Paul Microsoft Office Tutorials

Goal Seek is a very useful function in Excel which is used to determine a value which will produce a target figure in a calculation. In this example we have a spreadsheet of salaries and which have been awarded a 5% annual increase. The total increase for all salaries is £7,516, however we only have a budget for £6,200. Here is how we would solve the problem with Goal Seek:

using Goal Seek in Microsoft Excel

First download the Excel spreadsheet Salary Increases.
Next click the Data tab on the Ribbon, then click What If Analysis and select Goal Seek.
In the Goal Seek dialogue box enter D11 in the Set Cell field.
In the To Value field type the value 6200.
For the By changing cell field enter cell reference B3.
Finally click OK and Goal Seek will calculate the correct salary percentage increase to meet the budget of £6200.

This is a very simple Excel tool, but one which can save a lot of time. We cover this and many other time-saving topics on our Microsoft Excel 1 day courses. We specialise in on-site training and can deliver Foundation, Intermediate and Advanced levels of Excel at your own offices anywhere in the UK. For more information email info@pbatraining.co.uk or call free on 0800 2922842.

Constructing Simple Formulas in Microsoft Excel

May 26, 2010 Paul Microsoft Office Tutorials

Formulas in Excel perform calculations such as addition, subtraction and multiplication. They use the familiar arithmetic operators (+, – etc) however, formulas in Excel always begin with an equals sign (=).

Arithmetic Operators in Excel

+ Addition

– Subtraction

* Multiplication

/ Division

% Percent

Formula Construction

An example of a formula in Excel might be =2+2, which of course will return the answer 4. However, this would be a very restricted formula as it only returns one possible value. In practice, when we input formulas into an Excel spreadsheet we substitute raw data for cell references.

adding cell references in microsoft excel So an example formula might be =A1+B1. If the values in A1 and B1 are both 2 then the formula will return the answer 4 as before. However, we can now change the values in cells A1 and B1 and get different results without having to edit the formula.

As a general rule, we should try never to insert raw data into Excel formulas; it is almost always better to reference cells instead.

Here are some examples of Excel formulas:

Subtract B1 from A1: =A1-B1
Multiply A1 and B1: =A1*B1
Divide A1 and B1: = A1/B1

Editing Formulas

Formulas can be edited in the cell by double-clicking with the mouse. They can also be edited in the formula bar by selecting the cell, then clicking inside the formula bar.

Combining Arithmetic Operators

If we wish to combine operators in the same formula it is sometimes necessary to separate them with brackets. this is especially true if we mix multiple & divide with addition and subtraction. This is because the operators of multiply and divide take precedence. Consider the following example:

combining operators in microsoft excel We wish to add A1 and B1 then multiply the result by 20%. If we enter =A1+B1*20% we get the result 2.4. But this is incorrect, we should get 0.8.

combining operators in microsoft excel The reason we don’t is that because multiplication takes precedence over addition, Excel will first multiply B1 by 20% and then add A1 to the product. In order to add A1+B1 first we need to wrap them in brackets, so our formula becomes =(A1+B1)*20%. This gives the correct result of 0.8.

simple excel formulas Self-Test Exercise

First enter the data shown into cells A1 and B1.
Next add, subtract, multiply & divide the data in separate cells in column C.
Finally, in C5 add the data and calculate 10% of the product.

Attending a Microsoft Excel Training Course

Our 1 day Foundation Microsoft Excel course covers formulas and many other essential skills. It is suitable for people with no previous experience of using Excel and also those self-taught users who would benefit from a structured training course to ‘fill in the gaps’ and extend their working knowledge of the application. We run onsite Microsoft Excel training courses throughout the UK, please call free on 0800 2922842 to discuss your IT training needs. Alternatively you can email us on info@pbatraining.co.uk.