Category Archives for "Functions"

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 you want to build powerful worksheets that go beyond simple math or financial calculations. If you already consider yourself an IF function expert, you might want to check Tips 7, 9, 13, 14, and 19 […]

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. Even “experts” can learn new tricks or combine old tricks to work faster and more efficiently. As you scan these tips keep a “beginner’s mind.” A beginner’s mind helps you see new opportunities and insights. […]

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 will take you from novice to master in retrieving data from Excel lists. You can use VLOOKUP to, Look up prices, names, or regions from a list Use one item to find related items in […]

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 chart data into PowerPoint slides. Funnel charts are a favorite of marketing and sales for showing the volume and progress through different stages of the customer journey, from initial contacts, to qualified leads, to prospects […]

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 shows today’s date, custom holidays, and custom weekends. Changing the WORKDAYS.INTL function will also adjust the end date to account for non-working custom weekends and holidays. A Gantt Chart in Excel is a powerful way […]

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 how to create a Gantt chart in Excel and then enhance it with robust features. The tutorial starts with a simple Gantt chart using conditional formatting and a few functions and evolves into a project […]

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 window” looks into a much larger database. The result is a very functional reporting technique that is cool and takes little space in Excel reports or dashboards. If you put the large database in a […]

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 the power to scan historical data trends across large amounts of data. (Sparklines are only available in Excel 2010 and newer.) Download the Sparkline Advanced file tutorial. Normally, Sparklines use a cell reference, such as […]

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 charts, this method is backwards compatible with early versions of Excel. Click to download the tutorial example file on dynamically resizing charts using OFFSET and range names. The example includes both horizontal and vertical data. The […]

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 way to give your dashboard users the ability to scroll through a lot of data so they can see how data changes over time. This looks cool and what is better, it makes your work […]