Sunday, October 17, 2021
This simple invoicing system allows you to keep a list of products and prices in Excel, then use VLOOKUP or INDEX-MATCH to populate an invoice with the item and quantity you choose.

Use Excel’s INDEX-MATCH or VLOOKUP Functions to Populate Invoices and POs

A visitor asked how to set up a simple invoicing system in Excel. This is a common problem in many small businesses, divisions, and sales...
The extreme variability caused by seasonal sales makes it difficult to track and forecast your underlying sales trends. Here's how to solve that problem.

How to Create a Rolling Forecast of Seasonal Sales in Excel

The Excel chart below shows the typical saw-tooth pattern of seasonal sales. Seasonal sales have about the same pattern every year, every week, or both. In...
Which is faster: VLOOKUP? Or INDEX-MATCH? Here are the test results.

Excel’s Fastest Lookup Methods: The Tested Results

This article presents the results of my tests to find Excel’s fastest lookup method. I discussed the report workbook in A Volatile Workbook to Test Calculation...
Here's how select a value from a list of values from a worksheet's a Validation List. You can use the list to display interactive data in reports.

How to Select Values from a Validation List in Excel

Excel provides two ways to select a value from a list of values. I often use this feature in interactive Excel reports to select dates,...
A great way to distribute Excel reports is as a PDF file. Then you distribute the file. Here's how to save any number of Excel reports to one PDF file.

How to Save Multiple Excel Reports to One PDF File

A great way to distribute your Excel reports is to save them in a PDF file and then distribute the file. People who receive your...
Read a Text File with VBA in Excel, and Write the Text to a Spreadsheet

Read a Text File with VBA in Excel, and Write the Text to a...

"I need to write a text file into one row of my Excel spreadsheet, cell by cell, 20 characters at a time. It's urgent....
Pivot Tables aren't merely a way to interact with your data. You also can use them as a rich source of data for standard reports and analyses. And you don't need to limit formulas to GETPIVOTDATA; you also can use SUMIFS, SUMPRODUCT, and all other Excel functions with pivots. Here's how.

How to Set Up a Pivot Table as an Excel-Friendly Database

You can use a Pivot Table as a database in the same way that you can use Excel Tables and other Excel-Friendly Databases (EFDs). In...
The Internal Rate of Return calculation has very real problems, no matter where it's calculated. But with its MIRR function, Excel could offer a solution.

How Excel’s MIRR Function Can Fix the IRR Function

The calculation of an Internal Rate of Return is very appealing...at first glance. When you know the IRR of a prospective investment, you seem to...

Find Leading Indicators Using Automated Cross Correlations in Excel, Part 1

It all seems so simple... To improve your forecasts of sales or other measures, you simply need to find leading indicators...measures that are highly correlated...
Using Excel 2007 and above, you can create shaded areas in charts to specify areas of special interest. Here's how to do this using normal curves.

How to Create Normal Curves With Shaded Areas in Excel

Adding shaded areas to normal curves like this is a challenging task in Excel charting. But once you know how, it's not difficult to...

Latest Articles

To reduce errors in reports, you should foot and cross-foot them. But Excel's floating-point arithmetic gets in the way. Here's how to fix the problem with a standard deviation calculation.

How to Foot and Cross-Foot Excel Reports in a Floating-Point World

At first glance, the following report is an ordinary one. It merely sums an Excel Table by Product and Region, and then foots and...

Excel’s XLOOKUP Function Explained

Excel's XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't...

Excel’s VLOOKUP Function Explained

Excel's VLOOKUP function looks in the first column of an array and moves across the row to return the value of a cell. VLOOKUP is...
Advertisement
Advertisement