Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
Got a cool Excel trick you're proud of? I thought it'd be fun to hold an Excel Factor competition. Think X-factor (the TV show) but instead of massive fame and prize money (sorry, the budget doesn't extend that far yet), the prize is that you get to 'share the knowledge' with your Excel community and …
Did you know you can jazz-up your Excel Comments using Shapes, or even insert a Picture that conveys your message? Just take these weather themed comments I created: How to Change a Comment Shape In Excel 2007 and 2010 you first need to add a command to your Quick Access Toolbar. Note: I’m going to …
A few weeks ago one of our members, Perrin asked; “Is there a way to force a workbook to always open on a specific sheet? I have a workbook that has a directory and I would like users to always be directed to this sheet first when opening the workbook.” The answer is yes, but …
I can’t believe I haven’t already written a tutorial about Excel’s Go To Special feature. It is one of my favourite (a.k.a. favorite if you’re in the U.S.) closely behind VLOOKUP. If you import data from other systems you’ll invariably have to manipulate it to get it the way you need before you can analyse …
What if my side eBay bike business sold an extra 10%, what if I increased my price by $15 per bike, what if my costs increased by $5000 per year? What-if analysis is commonly done in Excel by saving different versions of the same workbook, or having a different sheet for each scenario which then …
Do you ever want to extract a list of unique records from a massive list like the one in column A below which has over 900 records? It would be tiresome to copy and paste the list and then delete the duplicate lines. Of course we could use the Remove Duplicates tool on the Data …
It's ok, no zebras get hurt in this process, it's actually all built in to Excel's Conditional Formatting 🙂 Conditional Formatting - Shade Alternate Rows To create this effect we need to create a new rule which uses a formula to determine which cells to format. Conditional Formatting Using a Formula Select the cells you …
We just arrived back from two weeks travelling around New Zealand with 3 other families. While there we managed to see some of the Rugby World Cup matches and do quite a bit of site seeing around the North Island. Our adventure started in Auckland where each family picked up a campervan which were to …
The other day I was in an Excel forum helping answer some questions. I like to help, because….well, I like to help, plus I like a challenge and it gives me ideas for things I can share with you. Anyway, I came across this tip (posted by someone else) and I had to read it …
If you work in large Excel files, Word documents or even e-mail, you may find it takes a long time to navigate and find the location you’re after. Hyperlinks are like a time machine in that they can take you there in an instant (but unfortunately they can’t take you back in time), and the …
If you find yourself opening the same workbooks each day when you start work, or when you do a particular job then you can save time by grouping these workbooks as a Workspace. It’s dead easy to do. Note: the Workspace feature was discontinued in Excel 2013 onwards. This only applies to Excel 2010 and …
Importing data to Excel from other programs and sources enables you to further analyse and generate reports or dashboards from what is often 1000's of rows of data. On the Data tab of the ribbon in the section Get External Data there are four options: From Access – a Microsoft Access Database From Web – …
I often create Excel workbooks that contain a series of sheets for each region (or product, month, or sales person) with a summary sheet at the front that gives me a total of all the sheets. But building workbooks like this can be onerous so I’d like to share with you a few tips for …
In Excel 2007 the graphics features are vastly improved. Not only have you got some instant designer quality flow charts (see image below), with the new and improved Shapes you can now apply effects like you find in Photoshop. Excel SmartArt I’m not going to go into a lot of detail with SmartArt. Other than …
You’re not likely to hear about Excel’s Camera tool on a training course and you certainly won’t find it on the ribbon. It is one of those insider tips that are hidden away in the toolbar options. What is Excel’s Camera Tool The camera tool takes a snapshot of a range of cells from somewhere …
If you work in Excel with data imported from other databases you’ll often find it doesn’t import it in the format you want. For example, I imported some Google Analytics data about our website traffic and the dates are formatted like this: Tuesday, February 1, 2011 While this looks like a date format, in my …
In my last tutorial I showed you how to insert a Subtotal using the Subtotal tool. If you’ve read that tutorial you will have seen how Excel can also conveniently Group and Outline your data so you can easily group and ungroup the rows to see varying levels of detail. Thankfully you can insert these …
Excel Filters are right at home with large tables of data. You can use them to limit the data displayed in your table to only that which meets one or more criteria you specify. When your data is filtered Excel hides the rows that do not meet your criteria. You can apply them to more …
Excel is 25! And to celebrate, Microsoft have put together two short video depicting the beginning, way back in the 80's right through to how Excel has transformed over the years. Watch it: To be inspired Get some ideas Just because you love Excel Give you an excuse to have some cake to celebrate! Your …
Imagine examining hundreds of rows of raw data in Excel in an attempt to find a pattern or trend. You’d go mad! Thankfully one of the tools we can use to make this task simpler is Conditional Formatting. The Conditional Formatting features in Excel 2007 are massively improved and now include many default Conditional Formats, …
Here are some easy solutions to Excel cell formatting frustrations that I get asked about all the time. Enter your email address below to download the sample workbook. Get Workbook By submitting your email address you agree that we can email you our Excel newsletter. Please enter a valid email address. Download the workbook used …
There’s a little known tool in Excel that will convert your text into speech and read your worksheet to you. It’s called Excel Speak Cells and not many people know about it because you won’t find it in any of the tabs on the Ribbon. It sounds like a gimmick, but it’s not just for …
An improved feature since Excel 2007 are Excel Tables (previously known as Lists). The purpose of an Excel Table is to aid with managing and analysing data. Excel Tables allow for easier sorting, filtering and formatting and interpretation of your data. They have some features that I think are MAGICAL, so keep reading to see why …
Learning how to customise a cell format in Excel allows you to not only format your data the way you want, but in some instances it can save you time. Before we dive in you need to know that despite how the text appears after you’ve set your custom cell format, the underlying value is …
You may have worked out by now that I am a big fan of Excel, after all that’s what I mostly write about. Even though I’ve been working with Excel for 15 years it wasn’t love at first sight. It was stressful and frustrating at first, but now that I’ve mastered it I enjoy teaching …
Creating Excel drop down lists, or Data Validation Lists as they're formally known, enables you to control the value entered into a specific cell by limiting the choices to those on a pre-defined list. Excel validates that the entry in the cell matches an item from your pre-defined list, or you can simply click on …
Excel Named Ranges enable you to give one, or a group of cells a name other than the default D3 for a single cell, or E3:F50 for a range of cells. Let's look at a simple example of how you might use a Named Range. Say you had a range of cells containing sales figures, …
Have you ever wanted your very own interactive Google Map dotted with locations relevant to your business? You might want to plot the locations of your stores or service agents. Or if you’re presenting performance data to management it’s a great visual representation of sales locations and density. These are just a few examples of …
