Category Archives for "Functions"

Top 21 Best VLOOKUP Tips and Tricks

Excel , Formulas , Functions , Tips , Top Best Tips

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 […]

Continue reading

Top 5 Best Excel Funnel Charts Sales and Marketing Love — One Funnel Chart You Should Never Use — And Two Dynamic Funnel Charts in PowerPoint!

Charts , Excel , Functions , Reporting , Tips , Top Best Tips

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 […]

Continue reading

Time and Project Management with an Advanced Gantt Chart in Excel

Charts , Excel , Excel Project Management , Functions , Project Management , Project Management , Time Management

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 […]

Continue reading

How to Build a Gantt Chart in Excel

Charts , Dashboards , Excel , Excel Project Management , Functions , Project Management , Project Management

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 […]

Continue reading

Vertically Scrolling Dynamic Sparklines Alongside a Database

Charts , Dashboards , Excel , Formulas , Functions

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 […]

Continue reading

Scrolling Dynamic Sparklines across a Horizontal Axis

Charts , Excel , Functions

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 […]

Continue reading

Creating Dynamic Charts in Excel that Resize Using the OFFSET Function and Named Ranges

Charts , Dashboards , Formulas , Functions

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 […]

Continue reading

Using INDEX to Scroll the X-Axis in Excel Charts

Charts , Dashboards , Excel , Functions

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 […]

Continue reading

Using INDEX to Create a Scrolling Window of Data

Charts , Dashboards , Excel , Functions

Use INDEX to simulate a scrolling box of data in cells anywhere on your worksheet. It gives your spreadsheet users the ability to see and scroll through a large amount of data in a small viewing area. You might use a small viewing area like this below a chart to show data from a large […]

Continue reading