How to Create a Report in Excel With Formulas

How to Create a Report in Excel With Formulas

In this article we will be looking at how to create a report with Excel using INDEX, MATCH and SUMIF.

Let’s assume that we have a Liqueur firm that has two reports that have been downloaded from another application, Microsoft Nav. click here for further details.

The first file is a Posted memo file

The second file is a master file with customer information

To analyse this data

  • On the Posted Memo file; Right-click on the B column and select Insert to insert a new column
  • To add the sales person name to the file we will use the index/match formula

=INDEX(customers!A2:A3123, MATCH(memos!A2,customers!B2:B3123))

The index is what you want to add to the formula, from the customer sheet all the salespeople that are listed.

The match is what you are going to match from both sheets. Select the first Acct# that is listed on the posted memo sheet, and then select all of the customer numbers that are listed on the customer sheet. for further detail, visit : http://guides.library.upenn.edu/excel

How to Create a Report in Excel With Formulas

  • Press enter and then double click on the fill handle to populate the entire column
  • Enter the sales people’s names in a separate column from the table.
  • Use the sumif Function to total each of the individual sales

=SUMIF($B$2:$B$445,K2,$J$2:$J$445)

Select the column where the criteria exists, (sales persons) – these must be absolutely referenced – click the F4 key on each column reference.

Select the individual criteria, (Larry)

Select the column that you would like added (the Amount)– these must be absolutely referenced – click the F4 key on each column reference.

  • Drag the formula down to complete all the sales for the Sales People
  • Select the data in our newly created table and click the insert tab
  • Select the Pie chart, from the Charts section
  • Select the 2D Pie and your chart will be created
  • Click on “Chart Title” and enter the desired text.
  • You will now have a clear visualization of you data
What is Excel

What is Excel

Sometime before 1993, Lotus 1-2-3 used to be the industry standard for the spreadsheet application. But later that year, tables turned when Microsoft Corporation introduced the modern version of Microsoft Excel. This propriety commercial application which is part of the extended Microsoft Office Suite family became an instant hit and quickly replaced Lotus-1-2-3 as the preferred Spreadsheet software at business places. To date, it has become essential to possess Microsoft Excel usage skills to work in any office or complete some personal spreadsheet projects. for related info, click here.

Microsoft Excel possesses basic spreadsheet features of organizing its workspace in a grid of cells that are arranged in numbered columns and rows which are used to organize data and manipulate arithmetic operations. It comes with a variety of functions and formulas that can be used to quickly perform some statistical, financial and engineering calculations. It can also prepare a pictorial representation of your data using histograms, line graphs, charts and some limited 3D graphical displays. for more about excel, click on  : http://www.nobles.edu/student-life/excel.cfm

What is Excel

To use Microsoft Excel, you need to ensure that any latest version of Microsoft Office suite is installed on your computer. For Windows 7 Ultimate and other older versions, click on the start button then programs. On programs menu, click on Microsoft Office and select Microsoft Excel from the list. For Windows 8.1 and Windows 10, click on windows logo at the bottom left corner followed by a click on the down arrow at the bottom. Proceed to the Microsoft Office group from where you can launch Microsoft Excel.…