migraine1

Pull Data, Not Teeth – The PivotTable Edition

In life as in business, we always strive to find the easiest ways of getting things done. Sometimes, however, the simplest methods involve cutting corners, obtaining short-term results but long-term headaches.

One of a long line of Excel features, the PivotTable is the best way to break your information down into more manageable chunks. In this post we’ll outline simple uses for the PivotTable along with a few tips to help you get the most out of your data.

excel pivottable

This data was converted into the PivotTable in 7 clicks

Uses for PivotTables

Anyone with a need to break down large data sets will find a use for the PivotTable. Sales managers, IT professionals, financiers and even marketers can save time and the get most from their information with this popular Excel feature.

A fantastic tool for summarising your data, the PivotTable has the ability to find hidden trends or relationships between data. Ok, so they’re not really hidden, but they may as well be surrounded by all that information. Sales managers rejoice: these complex tables can outline sales performance of team members over specified time periods, even down to products sold and of course much more.

Seminar

We empathise that the PivotTable has an off-putting name, but in truth, they are really easy to create and don’t even require a single formula to be written. To get started, just click any cell on your Spreadsheet and select PivotTable in the top navigation bar. Follow the prompts, tick a few boxes and complex tables will be created in front of your eyes.

As we touched on earlier, data can be easily transported, helping you to recognise trends within trends and look at your data more laterally. Again, for sales managers, one minute you’ll be able to see which team member has sold more coal to Newcastle in the past month, then you’ll be able to switch a few variables and see the trend of all products sold to Newcastle over the past few months.

Time saved is one of the major selling points for the PivotTable. These easy to create, complex tables become a powerhouse reference point for your every analysis requirements. From these tables, you’ll be able to create graphs and charts to better visualise your information. Ideal for presenting to colleagues and clients, you’ll look like a pro with just a few clicks of your mouse.

old-time-clock

PivotTable Tips

One benefit of grouping your data is that you can extract a subset of the grouped data onto a new worksheet. It’s really easy to do this too, just locate the group and double click in the total cell containing the data you’re interested in. Then all of the data that contributed to that total will be extracted onto a new worksheet.

Replace blanks cells with zeroes. When the PivotTable doesn’t have data for part of a row, you’ll get blank cells. It’s easy to get around this by right clicking any cell in the PivotTable and choosing options. In the layout and format tab in the format section, type 0 next to the field labelled “for empty cells show“.

a

Automatic updates mean that as soon as you change data in your original Spreadsheet, all you need to do is hit the refresh button and your PivotTable data will be bought up to date. Saving you time having to create a new table each time, Excel intuitively recalculates your figures. The larger the company or those with collaborative documents, the more useful this feature becomes. Imagine how many new PivotTables would need to be created if sales figures were updated daily.

Excel has some pretty good table styles and customisation options that help your data stand out and make it clearer to digest, not to mention brightening up your Spreadsheet. Change the colour and layout of your table using pre-set templates found in the top navigation bar.

Change the PivotTable summary function by right clicking inside the table and selecting “summarise data by” option. This allows you to look at the same data at a different angle. Quickly creating dynamic tables allows you to find those trends and even summarise them with a chart or graph for better reporting.

Sort your data by timescales quickly. Right click a date in the row field to group by months, years or quarters. Again this is a useful feature for measuring sales revenue and data change over time.

By employing these hints and tips, you’ll be able to save time and effort in reporting. What’s your favourite tip for helping you get the most out of your PivotTable?

Want to become an Excel expert? Attend one of Best STL’s training courses available London and UK wide.

Excel 2013: Use Timelines With PivotTable Data – MS Excel Training

new feature for microsoft office 2013See the timing of trends in your Excel 2013 PivotTable data with the new timeline feature. By default the data is split into months, though manipulation allows you to break this down into weeks, quarters, years or even days if you prefer.

If you make user of Slicers in your PivotTables and PivotCharts, you will be very comfortable with the timeline feature.

Timeline for PivotTables

  1. To add a Timeline, click in your PivotTable data area and from the Analyse ribbon select Insert Timeline from the Filter group.
  2. Select the date field required and your Timeline is now ready to be used to filter your date field chosen.
  3. You can change the specified dates by simply dragging each end of the timeline.

In the real world this could be a particularly handy tool on your performance dashboards. Once you’ve set everything up, you can then let users build their own view on the data.

Timeline for PivotTables

For more tips and features on Excel 2013 and other versions, browse MS Excel Training courses from Best STL, available London and UK wide. Speak to our a member of our team today to discuss the latest training offers along with your specific requirements. 

Excel 2013: 3 New Ways To Customise Your Charts – London Excel Courses

new feature for microsoft office 2013Whether you’ve decided to use a suggested chart to represent your data or already knew which one works best from the outset, a new toolbar in Excel 2013 allow you to customise your visualisation quicker.

Selecting the chart will automatically reveal two tool ribbons: Design & Format, both specifically designed to help you manipulate your Excel 2013 charts. Although the Chart Tools Layout tab no longer exists in 2013, the buttons it contained are still available, just in different places.

Excel 2013 charts

Three (new) buttons for chart formatting now appear at the top right corner of your chart; Chart Elements, Chart Styles and Chart Filters. Instead of digging through menus you can access these buttons overlaid on the chart.

Excel 2013 charts

 

Chart Elements

Add or Remove specific elements of your chart such as Data Labels and Gridlines. This way you can have as much or as little labelling and layout features as you desire.

Excel Charts 2013

 

Chart Styles

Change the colour and style of your chart with this simple formatting option. Scroll over each option to get a preview of how your new chart will look.

Excel Charts 2013

 

Chart Filters

Want to modify what data the chart includes? Previously you had to modify the data range in a fiddly way. Now you can hide and show data with the chart filters selecting them from the tick box menu, very similar to filtering data in a table. Customising your charts has never been so easy.

Excel Charts 2013

 

For more tips and features on Excel 2013 and other versions, browse London Excel courses from Best STL, available London and UK wide. With training levels ranging from beginner up to advanced and Excel VBA, there’s sure to be a course to suit your needs.

Microsoft Excel Training Courses 2013: Sparklines through Quick Analysis

new feature for microsoft office 2013Introduced in Excel 2010, Sparklines create charts within a cell. It can help to show one figure in context with others and illustrate trends. In this example a blank column is created for the sparklines before selecting the adjacent data.

In Excel 2013 the Quick Analysis feature has now brought Sparklines to the fore. Perfect for tracking performance over time, this new way of accessing them will give a quick insight into your data.

Sparklines in Excel 2013

How to: Highlight some Excel data in a table and look for the Quick Analysis tag to float over the bottom-right corner of your selection. Put your mouse over this icon to explore the options.

For more tips and features on Excel 2013 and other versions, browse Microsoft Excel training courses from Best STL, available London and UK wide. With our instructor-led training, you’ll be able to manipulate your data in ways you never thought possible, helping you save time and money. Choose your preferred course ranging from beginner to advanced level.