Microsoft Office Training — Microsoft Office Online Training

Fix Excel Chart Axis with a Ghost Series

by Mynda Treacy on July 22, 2014

Below is a set of Panel Charts. In this case they’re actually 4 separate charts aligned close together. Comparing one chart to the next isn’t easy since each chart uses a different vertical axis scale: If we could put all charts on the same axis then we could remove the 3 superfluous axes and ensure [...]

3 Steps to Easy Bullet Graphs in Excel

by Mynda Treacy on July 15, 2014

in Excel Charts

Bullet graphs were developed by data visualisation expert Stephen Few to address the need for visually rich displays of data in small spaces which are typical of a dashboard report. They enable you to compare your actual measure to a target/budget and also a qualitative range as denoted by the background fill. While Excel is [...]

Excel Protect Ranges with Different Passwords

July 8, 2014

Did you know you can set different passwords to protect specific ranges of your Excel worksheets? It’s called Allow Users to Edit Ranges and you’ll find it on the Review tab in the Changes group: Let’s say you have several different users of a workbook and you want to restrict each user’s editing rights to [...]

Protect and Unprotect All Sheets in a Workbook

June 18, 2014

Protect and Unprotect Worksheets in Excel

I recently had a client who has a requirement to protect and unprotect a lot of sheets. This was something they didn’t do very often, but when they did, they described the process of doing it manually as cumbersome. I’m sure that once you get more than a few sheets, protecting and unprotecting each one [...]

Customize the Ribbon in Excel

June 18, 2014

You can customize the Ribbon in Excel so that you add your own tabs and icons to run commonly used tasks and tools, or run your macros. NOTE : This applies to Excel 2010 and 2013 only. Modifying the Ribbon in Excel 2007 is a different process which I will cover in another post. Just [...]

Customize the QAT (Quick Access Toolbar) in Excel

June 17, 2014

You can customize the Quick Access Toolbar (QAT) in Excel so that you add icons to run your macros, or commonly used tasks and tools. Just follow the 8 steps below, it shouldn’t take you more than 5 minutes, if that. You can also modify the Ribbon to show custom tabs, groups and icons for [...]

Plot Excel Data on a Map

June 16, 2014

If you don’t have Excel 2013 then this post will be a bit like window shopping with an empty wallet. You get to try stuff on and test drive it, you just don’t get to take it home. Window shopping is still fun though, right? Excel 2013 has gone all iPad on us and now [...]

Excel Paste Special Operations

June 9, 2014

Copying and Pasting are two of the most common tasks you’ll perform in Excel. This is especially true for me and I can tell because the shortcut keys (CTRL+C for copy and CTRL+V for paste) on the new keyboard I got just last week are already showing signs of wear. Most of the time I [...]

Sorting in Excel PivotTables

June 5, 2014

My second Excel love is PivotTables (VLOOKUP is still my number 1). PivotTables make short work of summarising data and if you change your mind like the wind you can easily satisfy those whims with little more than a drag and a drop, unlike formulas which often require a lot more cajoling. Once you have [...]

Writing Excel Formulas Efficiently

May 27, 2014

I get a lot of questions from members and visitors to our site asking for help with formulas. It’s not surprising given they are one of the most useful Excel features. However, one of the repeating problems I’ve noticed recently are formulas that are not written efficiently. When I say ‘efficient’ I mean efficient for [...]