My Online Training Hub
Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
Back in June this year I had an idea to hold an X-Factor style competition where anyone could submit an Excel tip or trick that they were proud of. I didn't know how it would turn out, especially since there was no prize money. All I was offering was the opportunity to share an idea and be …
These Excel Factor tips were sent in by Zoran Stanojevic of Belgrade, Serbia. Words by Mynda Treacy Zoran has prepared 3 distinct ways we can apply hyperlinks that dynamically update depending on the selection we make. The selection can be controlled with either a data validation list or a spin button form control. Download the …
This Excel Factor tip was sent in by Marc Joannette of Montreal, Canada. Words by Mynda Treacy. Marc's job involves reissuing airline tickets. Part of this process requires a 'reason code' to be selected from a list. So he built a form where you can select a reason from a data validation list and the …
This Excel Factor tip was sent in by Roberto Mensa of Italy. Words by Mynda Treacy. Data validation lists are a great way to control the inputs to your worksheets and having one list dependent on another is nothing new. I did a post on how to do this using a named range trick. But, …
This Excel Factor tip was sent in by Jerry Beaucaire of Bakersfield, California. Words by Mynda Treacy Hyperlinks can make navigating your workbooks quick and easy but they (usually) take a bit of work to set up, especially if you use the Excel HYPERLINK Function. Excel HYPERLINK Function syntax =HYPERLINK(link_location, [friendly_name]) link_location is the cell …
This Excel Factor tip was sent in by Bob Cooke of Lincolnshire, England. Words by Mynda Treacy Last week Bob emailed me with an example of how he uses the INDEX, SMALL, IF and ROW functions to lookup a list and return multiple matches like this: It’s good timing as I actually had this …
This Excel Factor tutorial was sent in by Bryon Smedley of Bristol, Tennessee. Words by Bryon Smedley. 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 Excel Workbook and …
This Excel Factor tip was sent in by Kathy Carmel of Santa Barbara, California. Words by Mynda Treacy. Kathy uses Excel’s intersect operator to quickly SUM a cell, or range of cells at the intersection of two ranges. You’re forgiven if you’re thinking I’ve just swallowed a load of Excel syntax, so let me show …
This Excel Factor tutorial was sent in by Bryon Smedley of Bristol, Tennessee. I have to admit, this problem doesn't come up often, but when it does, this is a very slick way to eliminate hours of retyping or copy/pasting. Suppose you have two columns of data and you want to create a third column of data …
These Excel Factor tips are from Dr Isaac Gottlieb, professor at Temple University in Philadelphia. Every month Isaac publishes a series of Excel tips at his University website. He offered to share his tips with us….the problem I had was choosing just a few for this post, so feel free to visit his site and …
This Excel Factor tutorial was sent in by Bryon Smedley of Bristol, Tennessee. Suppose you receive a spreadsheet with calculations, but the calculations are stored as text, for display purposes only, and not used to actually derive answers. Without wanting to reinvent the wheel, you decide to try and use the documented versions of the …
These Excel Factor tips were sent in by David Kramer of Denver, Colorado, USA. Tip 1: Include or exclude parts of a projection Often when forecasting you ask a lot of ‘what if’ questions. A simple tool you can use to include or exclude specific items from a forecast is a control cell. In the …
This Excel Factor entry was sent in by Rob Anderson of Oxfordshire, UK. Project planning and other tracking worksheets can be enhanced with Conditional Formatting. Rob uses a clever trick to quickly colour code cells using conditional formatting like columns F:M below: Remember painting by numbers? It’s a bit like that. Where 1 = blue, …
This Excel Factor tutorial was sent in by Bryon Smedley of Bristol, Tennessee. There are times when you wish to determine how many unique entries are in a list. Wouldn’t it be great to be able to select a range of cells, like the one shown below, and have a function return, in this case, …
This Excel Factor tip was sent in by Shannon Hommel of the UK. If you’re setting up a template or a workbook for other’s to use and you can’t password protect it (for whatever reason), then the next best thing is to highlight the cells containing formulas. This is not only an Excel ‘Best Practice’, …
This Excel Factor tip is from Dr Isaac Gottlieb, professor at Temple University in Philadelphia. Words by Mynda Treacy Excel In Cell Charts aren’t actually charts at all, insofar as you won’t find them in the Charts menu in Excel. They’re actually a formula that you can insert in any empty cell, like you see …
This Excel Factor tutorial was sent in by Bryon Smedley of Bristol, Tennessee. Excel PivotTables are one of the greatest tools in the spreadsheet user’s toolkit. However, there is one tiny bit of functionality that appears to be missing: the ability of pivot tables to automatically update when information in the source data changes. Most …
The following Excel Factor entry was sent in by Daniel Ferry, founder of Excel enthusiast blog, Excel Hero. Often an Excel model needs a dynamic print area, as data that we wish to print may change over time. Watch how the print area for the table below (depicted by the dashed line around the data), …
The need to look up and match multiple criteria is quite common; however as with most things in Excel there are many ways to ‘skin a cat’, I'll share two options with you here. Matt Duncan from Florida sent in this a cool INDEX and MATCH array formula that allows you to match two criteria …
Importing data into Excel from other systems rarely goes without the need to fix formatting before you can even use it. Sandy R from Pittsburgh, PA sent in this Excel Factor entry. Using the REPT, LEN, FIND and MID functions Sandy can transform thousands of rows of data that comes into Excel like this: 3707.314278 …
If you use Excel Filters then you’ll love this time saving tip. This tip was sent in by Mohammed Mustafa of Jeddah, Saudi Arabia. Once you’ve set up your filters it requires at least a few clicks to apply them, but not anymore. With AutoFilter you can do it in two clicks! Taking the example …
How to Create a Reverse PivotTable - this is genius. This tutorial was sent in by Bryon Smedley of Bristol, Tennessee. There comes a time when you are presented with data in a cross-tabular format but your analysis requires that the data be formatted into a traditional table (or normalized) structure. Your report is in …
