Blogs: Consulting Tools and Templates

Tools and templates are indispensable to high-performance businesses. Any business framework, function, or process that you do multiple times, or that rely on high-precision should have tools, templates, and checklists to help you increase productivity and quality.

Tools and templates blogs include topics such as,

  • Templates for dashboards, reports, and presentations
  • Excel functions and descriptions for dashboards
  • Advanced Excel formulas and functions for data analysis
  • Tips on using software such as Excel and PowerPoint

Search for keywords in the list below by pressing Ctrl+F and typing the keyword

Consulting Tools and Templates

Consulting Tools and Templates

KPIs, Metrics, Validation Metrics, and Learning Loops
To improve any business, you have to measure and manage using Key Performance Indicators (KPIs) and metrics. But, to meet[...]
19 Top Tips on Excel Advanced Filter and Excel AutoFilter
Excel AutoFilter is a simple Excel feature for filtering lists. But, the Excel advanced filter has far more powerful filtering[...]
18 Top Tips for Creating Excel Lists or Excel Tables
People love putting their To Do List, Contact List, and Project Task List in Excel. These tips will show you[...]
10 Top Tips for Creating an Excel Budget or Excel Budget Template
Excel is the world’s most used tool for creating, aggregating, and analyzing budgets. Distributing an Excel budget template will reduce[...]
19 Top Tips on Excel’s IF Functions and Formulas
Excel’s IF function makes If…Then… decisions in your workbook formulas. The IF function and its variations are a must if[...]
Use an Excel-Based Decision Matrix for Critical Decisions
Prioritizing, justifying, and documenting decisions are easier when you use an Excel-based decision matrix. However, there are a few rules[...]
Top 26 Best Excel Conditional Formatting Tips and Tutorial
Conditional formatting in Excel applies formatting to cells when the cell’s content meets specific conditions, such as the content being[...]
Top 16 Best Excel Function and Formula Tips Every Excel User Should Know
These are the top 16 Excel function and formula tips every Excel user at any level of experience should know.[...]
Top 23 Excel Formatting Tips
These are the top 23 best tips on Excel formatting taken from research on top-ranked Excel websites and judged by[...]
Top 21 VLOOKUP Tips and Tricks
Use VLOOKUP to find and retrieve data from an Excel list. This list of the top 21 best VLOOKUP tips[...]
Show or Hide Data and Charts with Dynamic Excel Reports
With a few simple lines of Excel VBA your static Excel reports can become dynamic. A single click shows or[...]
Top 29 Pivot Table Tips
It is easy to say that Pivot Tables and Power Pivot are the two most powerful features in Excel. I[...]
Speedup Excel VBA Macros with Protect UserInterFaceOnly
Many Excel macros run on protected worksheets. If you use the little piece of VBA code in this article they[...]
Speedup Excel Performance
Regain faster performance with Excel 2013 (or newer versions) by turning off spreadsheet animation. Excel 2013 and newer versions use[...]
Download the Top 25 Dashboard Design Tips Sheet
A lot of readers were interested in the Top 25 Best Dashboard Design Tips article. It had the second highest[...]
Top 25 Tips on Excel Dashboards
This is a collection of tips that will help you create powerful Excel dashboards. This ranked list started by collecting[...]
Would You Like to Spy on Your Neighbor’s Dashboards? Here’s How.
Have you ever wanted to see how others have designed a dashboard? Or what another organization's Balanced Scorecard looks like?[...]
The Best Excel Checklist
This Excel checklist template has a lot of features. It’s easy to create and modify and it could save you[...]
Top 5 Excel Funnel Charts Sales and Marketing Love — One Funnel Chart You Should Never Use — And Two Dynamic Funnel Charts in PowerPoint!
This blog will show you five different ways to make funnel charts in Excel and two ways to link Excel funnel[...]
Use an Excel Pareto Chart to Improve Business Impact and Performance
There are two or three charts that every business person must know. One of those should be the Pareto Chart. This[...]
Top 25 Best Excel Dashboard and Chart Tips for 2015
Tony Robbins and other performance gurus who strive to improve personal performance teach that one of the most powerful ways[...]
Top 30 Best Excel Chart and Dashboard Tips in the “Excel Chart and Dashboard Quick Reference Guide”
Get your free copy now of the Excel Chart and Dashboard Quick Reference Guide. Quick Reference Excel Charts and Dashboards - US[...]
Time and Project Management with an Even More Advanced Gantt Chart in Excel
This advanced Gantt Chart in Excel has the basic scrolling Gantt Chart and custom weekends and holidays, but it also[...]
Time and Project Management with an Advanced Gantt Chart in Excel
This tutorial shows you how to take the basic Excel Gantt Chart and add conditional formatting so the Gantt chart[...]
How to Build a Gantt Chart in Excel
Gantt charts help you track the progress of tasks in a project. This and the following tutorials will show you[...]
Microsoft Excel 2016 Charts and Analytics: Are You Ready? Can You Wait?
Microsoft Excel 2016 was released September 22, 2015. Do you need its new charts and a few analytics gadgets it[...]
PowerPivot and Power View for Excel 2013
If you are using PowerPivot for Excel 2010, then click here. The following collections of links and resources will help[...]
Excel 2010 PowerPivot
To learn more about PowerPivot and PowerView for Excel 2013, click here. If you have Excel 2010 you have access[...]
Vertically Scrolling Dynamic Sparklines Alongside a Database
This Excel tutorial combines INDEX and Sparklines to create a window of vertically scrolling data and scrolling Sparklines. The “scrolling[...]
How do Sparklines work with Non-Numeric or Hidden Data?
Make sure you know how Sparklines work when their range includes non-numeric or hidden data. The data in the cell[...]
Scrolling Dynamic Sparklines across a Horizontal Axis
This tutorial combines dynamic range names with Sparklines so your dashboard users can scroll Sparklines through time. This gives your users[...]
Top 7 Sparkline Tips
Here are some tips on working with Excel Sparklines. If you want to... then do this…    Change the size[...]
Sparkline Tutorial – Creating a Miniature Chart in a Cell
This is a quick tutorial on how to create Excel Sparklines, miniature charts that fit within a single cell of[...]
Top 11 Best Dynamic Excel Chart Tips
Dynamic Excel charts automatically customize and update charts, allow users to scroll through time and select data, and give greater[...]
Creating and Checking Dynamic Named Ranges using Excel’s OFFSET Function
Use Excel's OFFSET and COUNTA functions to create dynamic range names that automatically adjust as data is added or deleted.[...]
Creating Dynamic Charts in Excel that Resize Using the OFFSET Function and Named Ranges
Dynamic charts using Excel's OFFSET and range names resize when data is added or deleted. Unlike using Tables for dynamic[...]
Creating Dynamic Charts in Excel that Automatically Resize using a Table
It's easy to create dynamic charts in Excel that resize if you use a Table. Excel 2007, Excel 2010, and[...]
Two Ways to Create Dynamic Charts in Excel that Resize – Which is Best for You?
There are two methods of creating dynamic charts in Excel - using Tables or using OFFSET and range names. Which[...]
Don’t Make this Mistake Using Excel’s OFFSET Function
Excel's OFFSET function will give you errors if you follow some instructions on the internet. Even the Microsoft Support page[...]
5 Top Budget vs Actual Excel Charts You Need
Here are five of the easiest to create and cleanest budget vs. actual (target) Excel charts. I spent three hours[...]
Creating Custom Excel Reports and Dynamic Sub-Lists Using Complex Criteria
Create sub-lists and custom Excel reports on a worksheet separate from the database using Excel’s Advanced Filter with complex criteria,[...]
Creating Dynamic Excel Lists and Custom Reports from One Master Excel List
A highly valuable Excel skill is being able to create dynamic sub-lists, custom reports, or chart data from a larger[...]
Excel FUNC-tions (Parody of Uptown Funk)
What do you get when you concatenate soul music, jazz, and R&B? You get Uptown Funk. But, this version has[...]
Using INDEX to Scroll the X-Axis in Excel Charts
Scroll through time in your Excel charts by combining an INDEX function with a scroll bar. This is a great[...]
Using INDEX to Create a Scrolling Window of Data
Use INDEX to simulate a scrolling box of data in cells anywhere on your worksheet. It gives your spreadsheet users[...]
Using INDEX and MATCH to Retrieve Data and Chart with Multiple Criteria
Use multiple criteria to retrieve data from tables that do not have a unique identifier or index for each row[...]
Using INDEX and MATCH to Retrieve Excel Data
Use INDEX and MATCH to retrieve data from an Excel table when you want the greatest flexibility and speed in[...]
Using VLOOKUP with multiple criteria from drop-down lists
VLOOKUP cannot accurately retrieve data from tables where the leftmost column does not have unique text values. But, with this[...]
Using VLOOKUP and MATCH for a two-way look up across the column header
Most VLOOKUP formulas specify an exact column to pull the data from, but if you use MATCH it’s easy to[...]
Troubleshooting errors in VLOOKUP
If the lookup_value is not found in the leftmost column, VLOOKUP returns a #N/A error. There are simple ways to[...]
Using VLOOKUP to retrieve data from an Excel table
VLOOKUP is a simple way to retrieve data from a table in Excel, but you must understand its limits. VLOOKUP[...]
VLOOKUP and the combination of MATCH and INDEX retrieve cell contents from a table. But, there are important differences between[...]
Using VLOOKUP, INDEX, and MATCH to find and extract data from tables and databases
VLOOKUP is a foundation function for using information in lists or databases. An easy way to remember what it does[...]
Quickly Create Range Names
Range names are great! Look for more info on them in other tips. With that advertisement said,… You don’t have[...]
Use Only Allowed Characters in Range Names
Range names must start with a letter, underscore, or backslash. Following characters must be letters or numbers, periods, or underscores.[...]
Create Range Names with Upper and Lower Case, but Type them in Lower Case as a Cross-Check
When you create range names use upper and lower case lettering. For example, rngDataEntry. Excel remembers the upper and lower[...]
Use a Structured Prefix to Identify Range Name Types
Range names are great! Did I say that before? They can make formulas much more understandable. And they can make[...]
Use a Major and Minor Naming Convention when Naming Ranges
Keep your range names so they are easy to understand and find by using the structured prefix described in another[...]
Creating Custom Date, Number or Percentage Formats
Excel already has many numeric and date formats built into its Format Cells dialog box. But, you can create any[...]
Custom Numeric Format Layout
Look at the Type list in the Number tab of the Format Cell dialog box. These format specifications follow a[...]
Custom Numeric Formatting Symbols
Excel has custom formatting symbols for just about everything, including colors, text, and special characters. I’d recommend you click on[...]
Predefined Date and Time Formatting Symbols
Before you begin creating custom date and time formats it’s a good idea to look through the predefined date and[...]
Custom Date and Time Formatting Symbols and Examples
You can make almost any date and time format you want with Excel’s custom formatting. For example, I don’t like[...]
Change All Formats on a Worksheet While Using Minimal Memory
If you want to change most of the number, date, or time formats on a worksheet, don’t change them cell[...]
Hiding Numbers with a Custom Format
If you want to hide a number in the display, but keep it for use in calculation, then create a[...]
Hiding Zeros in Cells or Worksheets
As you would expect in Excel there are a couple of ways of hiding zeros. In fact, there’s a bunch[...]
Format cells to hide numbers, but still use them for calculation
An easy way to hide numeric contents, but still have it available for calculation is to give them a custom[...]
Disguising Numbers on Entry So They Can’t be Read
You’ve seen how numbers are hidden when they are entered in password fields. You can do the same thing in[...]
Creating User Prompts and Help with Custom Formats
Including text with numbers is a handy trick you can use to warn users of incorrect entries or to prompt[...]
Formatting Custom Numbers in Colors
You can specify colors in any of the custom number segments. The valid numbers are, [Black] [White] [Red] [Green] [Blue}[...]
Formatting a Zip Code
To format a Zip Code, select the cells, then right-click and choose Format Cells. In the Format Cells dialog box,[...]
Formatting a Social Security Number
To format a Social Security number, select the cells, then right-click and choose Format Cells. In the Format Cells dialog[...]
Formatting an In-Country or International Telephone Number
To format a telephone number, select the cells, then right-click and choose Format Cells. In the Format Cells dialog box,[...]
Fitting long numbers into narrow cells
Doesn’t it drive you crazy when a number doesn’t fit in a cell and making the cell wider messes up[...]
Find all Conditional Formatting on a Worksheet
Ever try to find conditional formats on a worksheet when the worksheet is showing all cells with unformatted appearance? It’s[...]
Use TEXT and & to combine text, numbers, and dates for dynamic titles and labels
A great way to make your charts and dashboards more usable and include dynamic titles, legends, and text is with[...]
How to Combine Bubble and Column Excel Charts – Creating a Dynamic Four Quadrant – Matrix Model
A static Four Quadrant - Matrix Model is fine if you’re working with something like the Steven Covey - Eisenhower[...]
How to Create a Static Four Quadrant – Matrix Model in an Excel Chart
The Four Quadrant - Matrix Model is a valuable decision making tool. This article will show you how to create[...]
Moving Data Labels on an Excel Bubble Chart
Bubbles and labels can get crowded and hard to read. Make them easier to read by either dragging individual labels[...]
Adding Labels with Multiple Lines to the Bubbles in an Excel Bubble Chart
To make your chart one you can make decisions from it may need more information than normally shows in an[...]
Adding Labels to the Bubbles in an Excel Bubble Chart
You can apply data labels to the bubbles to show the data they represent by right-clicking a bubble so the[...]
Scaling the Size of the Bubbles in a Bubble Chart
If you have only a few bubbles in your bubble chart and do not specify a Z value, the initial[...]
Inserting a Background Picture into the Chart’s Plot Area to Create Static Quadrants
You can insert a picture behind any Excel chart. To create a static four quadrant background you will need to[...]
Smoothing Excel Chart Data with a Moving Average
Sales, marketing, and customer data can be so erratic that it’s hard to see patterns in your Excel chart or[...]
Smoothing Excel Chart Data with Dynamic Smoothing
Smoothing Excel chart data with a fixed moving average works when you have experience with the data and how variable[...]
Smoothing Excel Chart Data Using a Weighted Average
While smoothing using a weighted average is easy to do and understand. But, it has a problem. It gives equal[...]
How to Create a Dynamic Chart with Drop-Down List
Want a way to quickly create a dynamic Excel chart or dashboard? Your users will be able to select the[...]
How to Create Dynamic Range Names for Automatically Expanding Menus
Range names are words you assign to cell references or formulas. You can use them instead of a cell reference[...]
How to create a dynamic chart using a Data Validation list and VLOOKUP
This could very well be the fastest and easiest way to create a dynamic chart. It’s a great way to[...]
Dynamically Control Excel Chart Trend Lines with Check Boxes and Drop-Down Lists
You can get very creative using check boxes and drop down lists to give users the ability to view different[...]
Dynamically Change Trend’s in a Chart Using Excel’s Drop-Down and INDEX
Another way we can help our Excel dashboard users is to give them the ability to select between two or[...]
Using a Check Box to Dynamically Show or Hide Trend Lines in Excel Charts
Showing or hiding a trendline in Excel charts or dashboards with a check box gives your users more power. Some[...]
Editing TREND and Array Functions
TREND and some other functions that work on an array (a collection) of cells are array formulas. They use an[...]
Using the TREND Function to Forecast Future Values in Excel Charts and Dashboards
The TREND function will also forecast future points on the trend line. The TREND function format is, =TREND(known-y’s,[known_x’s],[new_x’s],[constant]) In the[...]
Using Excel’s TREND function to Create Trend Lines on Excel Charts or Dashboards
To see the trend data and to create dynamic trend lines you can control with menus you need to use[...]
Correlation does not Imply Causation when Calculating Trend Lines
So, just because you got a high R-squared on a trend does not mean you can do predictions with your[...]
What is a Good R-Squared Value or Is the Fit Good for a Trend Line?
You’ve probably heard the rule of thumb that if R-squared is .7 or better then the trend line has a[...]
Creating an Excel Chart to Practice Trend Lines
If you want to follow along, this basic chart will first be used to create an embedded trend line that[...]
Excel Trends Tutorial – Creating Simple and Dynamic Trends on Charts
This Excel tutorial takes you from adding a simple trend line on an Excel chart to creating dynamic trend lines[...]
Changing Trend Line Options on Your Excel Chart
In the previous article, Creating a Trend Line on Your Excel Chart, you could see how easy it is to[...]
Creating a Simple Trend Line on Your Excel Chart
This first method of creating a trend line will add a trend line to a chart without showing any trend[...]
Amazing Artistry with Excel Drawing Objects!
Many years ago I took classes in Sumi-e and calligraphy because I love how their simplicity can communicate so much[...]
Make Small Changes to a Hand-Drawn Line
Excel has some excellent drawing tools. In fact, there is a Japanese artist who has created amazing artwork using Excel.[...]
Quickly Change an Object’s Height or Width
Change an objects height or width quickly using the keyboard. Click on the object to select it. Now, hold down[...]
Select all Objects on a Worksheet
To select all objects on a worksheet, press Ctrl+G to display the Go To Special dialog box. Select the Options[...]
Moving Objects a Small Amount
When you want to position drawn objects to an exact position don’t use the mouse. Instead move the object into[...]
Select and Change Some Objects on a Worksheet
To select some objects on a worksheet, but not others, click on the first object, and then hold down Shift[...]
Troubleshooting Problems in Excel Charts: The X- and Y-axis are Reversed
Excel doesn’t always get it right when it automatically assigns one set of labels to the X-axis and the other[...]
Troubleshooting Problems in Excel Charts: Missing Data Creates an Ugly Chart
There’s another tip in this blog, “When data points are bad or missing, how do you create a usable and[...]
Troubleshooting Problems in Excel Charts: Bars or Columns are in the Wrong Order
Excel charts data series in the order they appear in the worksheet. But, sometimes for presentation purposes you want them[...]
Troubleshooting Problems in Excel Charts: X-Axis Doesn’t Chart Correctly
You’re in a little hurry, you knock out a hand-entered table in the worksheet, drag across the data and insert[...]
Save Time and Work by Creating Default Chart Templates for Excel
I’m willing to bet that the Pareto principle, the 80/20 rule, applies to the charts you build. The majority of[...]
Create Additional Chart Templates
You probably use three or four types of charts on a consistent basis. So, the way to save more time[...]
Set the Default Chart Template
Excel’s default template initially creates charts as column charts with a legend. The original default chart is the chart you[...]
Creating a Chart with a Custom Template
When you want to create a chart using one of the templates you’ve previously created with your choice of chart[...]
Deleting Chart Templates and Resetting the Default Chart Type
It’s easy to delete or rename templates by going into the Change Chart Type dialog box and selecting the Manage[...]
A Quick Checklist for Clean, Readable Excel Charts
We all face infoclutter. What we don’t need is infoclutter in the charts we create. The following tips apply to[...]
Keystrokes for Troubleshooting Formulas
Go ahead admit it. You’ve made a mistake, at least one. I know I’ve made tons of them, especially in[...]
Watching Two Different Parts of a Workbook at the Same Time
Every parent has wished they could be two places at the same time. I haven’t found a way to do[...]
Document your formulas right in the cell!
I’ve had worksheet amnesia so many times it’s not funny. I’ve learned not to make my formulas too complex or[...]
Inserting Multiple Lines (Line Breaks) in a Cell with Alt+Enter
I bet every Excel user has had it happen. You want to type a long line or paragraph in a[...]
Creating an Excel Chart with Two Rows of Labels on the X-Axis
When the X-axis is crowded with labels one way to solve the problem is to split the labels for each[...]
When data points are bad or missing, how do you create a usable and decent looking Excel Chart?
You may have faced the problem of having a chart where some of the data doesn’t exist or it’s entered[...]
Troubleshooting Your Data Selection for Excel Charts, Databases, Tables, and Lists
A lot of the work you and I do in Excel involves lists, tables, or large chunks of data. Learning[...]
Exporting Data for Import into Microsoft Excel
You can’t always link Excel worksheets, Pivot Tables and PowerPivots directly to a database. Sometimes you have to go back[...]
Troubleshooting Text Files Imported into Excel
Importing data into Excel can be extremely easy or it can be so frustrating you have to leave your desk[...]
Connect Emotion and Analytics To Get Action and Impact
You can have great analysis and good answers, but if you can’t communicate and create action your work could be[...]
Easy and Awesome Infographics and Stock Photo Designer for Use with Excel and PowerPoint
For those of us who aren’t graphic designers, but need to create backgrounds, infographics or designs with stock photos, icons,[...]
Creating Dynamic Excel Chart Legends that Link to Worksheet Cells
Want to create Excel chart legends that show additional information? Wouldn’t it be great to have a multiline legend that[...]
Creating Dynamic Excel Chart Titles that Link to Worksheet Cells
You don’t have to settle for Excel’s static chart titles. Your Excel chart titles can be dynamic and link to[...]
Creating Dynamic Excel Timelines That Scroll
Last week’s newsletter described how to create a static event timeline in an Excel chart. In this article you'll learn[...]
Adding the Developer Tab to the Excel Ribbon
Reading this will teach you one of the secret incantations need to move from mid-level Excel user to Excel power[...]
Selecting Hard-to-Click Chart Elements
In complex charts it may not always be easy to select the specific chart element you want to delete or[...]
Excel Chart Shortcut Key
Using an Excel chart shortcut key makes it quick and easy to create and view a chart. If you like[...]
Create an Excel Timeline Chart to Manage Your Projects and Events
Excel timeline charts are a great tool in any professional’s toolkit. You can insert Excel timelines in your Excel workbooks[...]
Adding a Data Series to an Excel Chart
It happens to everyone, it’s the reason Excel was created – the data changes and your Excel chart must change.[...]
Quickly Change Excel Chart Styles and Themes
If you’ve been creating charts in Excel 2007 or a newer version for very long you know how easy it[...]
Undoing All or Part of an Excel Chart’s Original Theme
It’s easy to spend a lot of time formatting a chart, but if you’re like me you try different things[...]
Aligning Excel Charts and Objects on a Worksheet
If you want professional looking dashboards you need to make sure that charts and objects you draw align with each[...]
Creating an Actual vs Target Chart in Excel using Floating Markers
Almost everyone in business has heard the maxim, “What you measure is what gets managed.” We know that measuring is[...]
Create Excel Charts and Dashboards with Color Alerts for Actual and Target Values
Your Excel charts should alert you with color differences between actual and target values. Here’s a quick way to create[...]
Hiding Worksheet Data Used in Excel Charts and Dashboards
Recently an associate called me with an Excel 2010 problem. She was asked to create an elegant worksheet that pulled[...]
Show Excel Pie Chart Details with an Exploded Bar Chart
Show the details in a pie chart by exploding sections into a bar chart. To get the big picture of[...]
Telling the Story Behind Your Data – Awesome Videos with Hans Rosling
If you want to make an impact, you have to put a gripping story behind your data. You can have[...]
Finding the Right Metrics for Your Excel Dashboard
I’ve noticed that dashboards often get started when a manager says something like, “We have to get a handle on[...]
Experiment in Excel with Sample Census Data
Creating sample data files to experiment with in Excel is about as much fun as weeding the lawn. So to[...]
Creating Cascading Drop-Down Menus in Excel
What do you do when you want to give users the ability to select from 20 or more choices in[...]
Beware of Excel and Complex PDFs
Warnings on Using Adobe Acrobat I’ve only written 26 technical/business books and done performance consulting for 30+ years so you’d[...]