Category Archives for "Formulas"

Top 16 Best Excel Function and Formula Tips Every Excel User Should Know

Excel , Formulas , Functions , Top Tips

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

Continue reading

Top 21 VLOOKUP Tips and Tricks

Excel , Formulas , Functions , Top 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

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

Top 11 Best Dynamic Excel Chart Tips

Charts , Excel , Formulas , Top Tips

Dynamic Excel charts automatically customize and update charts, allow users to scroll through time and select data, and give greater analytic power. What’s not to like? There are so many way to make charts dynamic so they update and customize automatically, that at first you don’t know what you don’t know. So, here is a […]

Continue reading

Creating and Checking Dynamic Named Ranges using Excel’s OFFSET Function

Charts , Dashboards , Excel , Formulas

Use Excel’s OFFSET and COUNTA functions to create dynamic range names that automatically adjust as data is added or deleted. Normally a named range refers to a fixed set of cell references, for example, $B$7:$B$12. However, if the OFFSET formula is used to calculate the cell reference, the range name will change as the data […]

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

Two Ways to Create Dynamic Charts in Excel that Resize – Which is Best for You?

Charts , Dashboards , Excel , Formulas

There are two methods of creating dynamic charts in Excel – using Tables or using OFFSET and range names. Which is best for you? The easiest method uses the TABLE feature of Excel 2007, Excel 2010, and Excel 2013. A second method works for all versions of Excel, even those from way back, and uses […]

Continue reading

Don’t Make this Mistake Using Excel’s OFFSET Function

Excel , Formulas

Excel’s OFFSET function will give you errors if you follow some instructions on the internet. Even the Microsoft Support page found here makes this mistake, How to use defined names to automatically update a chart range in Excel https://support.microsoft.com/en-us/kb/183446 In their example, Microsoft puts all data in one column and references that data column in […]

Continue reading

Using INDEX and MATCH to Retrieve Data and Chart with Multiple Criteria

Charts , Excel , Formulas

Use multiple criteria to retrieve data from tables that do not have a unique identifier or index for each row . This example uses two drop-down lists to select and then chart Product and Region for the months Jan through Dec. Concatenate (join) two columns together to create a unique key you can use to […]

Continue reading

Using INDEX and MATCH to Retrieve Excel Data

Charts , Excel , Formulas

Use INDEX and MATCH to retrieve data from an Excel table when you want the greatest flexibility and speed in retrieving data. INDEX and MATCH takes a little more work to create than VLOOKUP, but it is far more flexible and powerful. It also enables you to animate charts. One of the most powerful features […]

Continue reading
1 2 3 5