Category Archives: How to

How to do things in Excel. Blog posts describing (often with screenshots) how to carry out tasks or functions in Excel. Accounting and Financial features included.

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.

Excel 2013: Complete data entry quickly with Flash Fill

new feature for microsoft office 2013Save time and effort with Flash Fill for Excel 2013. Just one in a long line of nifty improvements to help you work more efficiently.

Flash Fill for Excel 2013 notices patterns in your data entry and then auto completes the remaining, so there’s no need to use formulas or macros to do this. Data gets filled in automatically. Previously you would have to use variations on LEFT(), RIGHT(), MID() plus a few other supporting functions to do this.

For example the Flash Fill feature will automatically complete a list of surnames after starting to type the second surname. It recognises patterns and predicts what data to fill in for you. Start typing the initials and Flash Fill fills the list for you.

Flash Fill for Excel 2013

Top Tip: Flash Fill also recognises text case. After typing a name in upper case, clicking Flash Fill on the Data Ribbon fills all the rest of the names in upper case.

Flash Fill for Excel 2013

 

Whether you’re splitting out email addresses or stripping out surnames, Flash Fill is a very useful feature.

For more tips and features on Excel 2013 and other versions, browse One Day Excel courses London from Best STL, available UK wide.