Post image for Excel Gantt Chart

Excel Gantt Chart

by on May 22, 2013

in Excel,Excel Charts,Microsoft Office Training,Online Training

Gantt charts are commonly used in project management and while Excel doesn’t have a native Gantt chart in its chart library we can easily create one. Don’t get too excited, it’s a very basic one. If your job involves complex project management then you probably need a more sophisticated tool like Microsoft Project. However, for [...]
Post image for VLOOKUP Multiple Values in Multiple Columns

VLOOKUP Multiple Values in Multiple Columns

by on May 16, 2013

in Excel,Microsoft Office Training,Online Training

A few weeks ago David T asked me to help him understand a VLOOKUP formula in a workbook he’d inherited from a colleague who had left his company. It was a VLOOKUP formula like nothing I’d ever seen before so I thought I’d share it with you. Drum roll….. =VLOOKUP(E2&F2,IF({1,0},First_Name&Last_Name,Grade),2,FALSE) David’s question was ‘what’s the [...]

Excel BETWEEN Formula

May 9, 2013
Thumbnail image for Excel BETWEEN Formula

A little while ago Brendan asked me how he could check if values in a list fall between two limits. Now, it’d be nice if there was a BETWEEN Function, but there isn’t. Hence why the title of this tutorial isn’t ‘BETWEEN Function’, it’s ‘BETWEEN Formula’. I’ve got two options for you: BETWEEN with MAX [...]

read more

Excel Tables as Source for Data Validation Lists

April 30, 2013
Thumbnail image for Excel Tables as Source for Data Validation Lists I’ve set up a table with some team names that I want to use in a Data Validation list. The reason I formatted my list in an Excel table is because I want the range to dynamically update when I add or remove teams from the list. My table’s name is Table1, as you can [...]
read more

Excel Step Charts

April 25, 2013
Thumbnail image for Excel Step Charts I recently came back from a family skiing vacation in Whistler, Canada, which by the way was fantastic. Here we are at a lookout on a snowmobile trip we took. Anyway I digress…. one of the key pieces of information you want to know as a skier (or snowboarder) is how much snow is on [...]
read more

Excel Advanced Filters

April 18, 2013
Thumbnail image for Excel Advanced Filters To be honest I don’t use Advanced Filters much. I tend to use the regular AutoFilter, or if I need more I’ll use the Custom AutoFilter. Not because they’re necessarily better, it’s probably more out of habit than anything else.   But Advanced Filters have a couple of advantages over regular filters:   The filter [...]
read more

No More Back and Forth Between Tabs

April 10, 2013
Thumbnail image for No More Back and Forth Between Tabs Moving back and forth between worksheets in a large workbook can be tedious. Of course you can use the shortcut keys CTRL+Page Up or CTRL+Page Down to quickly move from one worksheet to another (and CTRL+Tab to move between workbooks). However if there’s a lot of worksheets in a file the shortcut keys aren’t going [...]
read more

Excel PivotTable Month Comparison [Video]

April 2, 2013
Thumbnail image for Excel PivotTable Month Comparison [Video] In this last tutorial, in my 3 part series on PivotTable tips and tricks, I’m going to show you how to include the month on month change. In the example below we can see that beverage sales in Feb were $10,391 less than the previous month, and in March they were $4,622 less than February: [...]
read more

Excel PivotTable Group Data [Video]

March 26, 2013
Thumbnail image for Excel PivotTable Group Data [Video] In this second tutorial, in my 3 part series on PivotTable tips and tricks, I’m going to show you how to group data like dates into years, quarters or months, and ages into groups like this PivotTable below: Watch the Video For best viewing watch the video in 720p HD full screen. Download the Workbook [...]
read more

Excel PivotTable Add Percentage of Total Column [Video]

March 19, 2013
Thumbnail image for Excel PivotTable Add Percentage of Total Column [Video] Excel PivotTables make light work of analysing data but there are a few tricks once you get past the basics. In this first tutorial, in my 3 part series on PivotTable tips and tricks, I’m going to show you how to add a percentage of total sales to your PivotTable like this one in column [...]
read more

← Previous Entries