Blog • Page 17 of 24 • My Online Training Hub

September 23, 2014

Change the Color of Words in Text

One of our students (hi Leah 🙂 ) sent me a question last week asking how she could change the

September 15, 2014

Excel Custom Data Validation to Limit Entries

In this tutorial we're going to look at how Santa is using Excel Custom Data Validation to limit the number

September 8, 2014

Excel Pivot Tables Year on Year Change

PivotTables are a treasure trove of features and one that has been brought out of the dungeons in more recent

September 1, 2014

Deleting Rows in Excel Containing Blanks Returned by Double Quotes

We should all be using Go To Special to quickly remove rows containing blanks, but if those blanks are returned

August 26, 2014

Text to Columns with an Excel Formula

If you reformat data brought into Excel from an external source regularly then you’ve most likely come across Excel’s Text

August 19, 2014

Excel Table Absolute Structured References

Excel Tables are one of my favourite features. They make working in Excel much more efficient in so many ways,

August 12, 2014

Excel Slicers – Everything You Need to Know

Excel Slicers are a professional way to enable users to easily and intuitively interact with your reports, filtering data in

August 5, 2014

Excel Formula to Spread Income or Costs Over Months

WARNING: this tutorial contains some advanced formula concepts which may incur the following side effects: Negative side effects: Headache, urge

July 22, 2014

Fix Excel Chart Axis with a Ghost Series

Below is a set of Panel Charts. In this case they’re actually 4 separate charts aligned close together. Comparing one

July 15, 2014

3 Steps to Easy Bullet Graphs in Excel

Bullet graphs were developed by data visualisation expert Stephen Few to address the need for visually rich displays of data

July 8, 2014

Excel Protect Ranges with Different Passwords

Did you know you can set different passwords to protect specific ranges of your Excel worksheets? It’s called Allow Users

June 18, 2014

Protect and Unprotect All Sheets in a Workbook

I recently had a client who has a requirement to protect and unprotect a lot of sheets. This was something

June 18, 2014

Customize the Ribbon in Excel

You can customize the Ribbon in Excel so that you add your own tabs and icons to run commonly used

June 16, 2014

Plot Excel Data on a Map

If you don’t have Excel 2013 then this post will be a bit like window shopping with an empty wallet.

June 9, 2014

Excel Paste Special Operations

Copying and Pasting are two of the most common tasks you’ll perform in Excel. This is especially true for me

June 5, 2014

Sorting in Excel PivotTables

My second Excel love is PivotTables (VLOOKUP is still my number 1). PivotTables make short work of summarising data and

May 27, 2014

Writing Excel Formulas Efficiently

Struggling with Excel formulas can turn simple tasks into time-consuming challenges, leaving you frustrated and behind on work. Fortunately, a

May 20, 2014

Create Hyperlinked List of Files in Subfolders

I wrote a macro a few weeks ago which created a list of files with hyperlinks to those files, but

May 14, 2014

Interactive Excel Web App Dashboard

Warning: this is a long post. Get supplies so you don’t dehydrate or suffer from low blood sugar levels before

May 6, 2014

Is Your Line Chart Lying?

Charts are great at revealing trends, patterns and relationships in your data. We love how they enable us to glean

April 29, 2014

Conditional Formatting PivotTables

Conditional Formatting in PivotTables has its ups and downs. Unfortunately I find them mostly ‘downs’ but let’s not dwell on

April 22, 2014

Excel Chart Templates

If you find yourself repeatedly spending a few minutes customising your chart colours, formatting the title, legend, axis labels etc.

April 16, 2014

Create Hyperlinked List of Files in a Folder Using VBA

So you have a load of PDF's, or scanned documents, or some nice pictures of your stamp collection*, and you

April 9, 2014

Macro Enabled Excel Templates

What You Can Do With Macro Enabled Templates This is up to you. Using a template allows you to design

April 9, 2014

Trusted Locations

If you want Excel to stop asking you if you want to enable macros every time you open a macro

April 1, 2014

Unhide Multiple Worksheets in Excel

I’m sure most of us have experienced frustration at Excel’s refusal to allow us to unhide multiple worksheets. This is

April 1, 2014

Create a Personal Macro Workbook PERSONAL.XLSB

When you create a macro in Excel, by default, the macro works only in the workbook that contains it. But

March 25, 2014

Display Missing Dates in Excel PivotTables

Q: How do you get the PivotTable to show the missing dates in your data? A: Not as easily as

March 21, 2014

Excel Custom Number Formats

One of the most useful features in Excel are custom number formats yet they remain largely underused. In my Excel

March 12, 2014

Excel 3D Financial Reports

Last week I wrote about how I used Excel to design a Minecraft cake. The response to that post was