Book Image

Excel 2010 Financials Cookbook

By : Andre Odnoha
Book Image

Excel 2010 Financials Cookbook

By: Andre Odnoha

Overview of this book

<p>Excel is one of the mostused software tools in the world and just about every business has a copy somewhere. Despite its power and flexibility it is not always clear how to use it to perform some of the most important tasks in any business: organizing, analysing, and presenting financial information.<br /><br />Excel 2010 Financials Cookbook contains a rich collection of useful techniques for handling financial data in Excel. From integrating data from a variety of different sources, through organazing and analyzing financial data, to presenting it in a variety of graphical forms, this book has you covered.<br /><br />The book deals first with "normalizing" financial data -- that is, bringing data from a number of different sources into a single format where you can analyze them together. Then you'll learn techniques for managing and analyzing the data before discovering ways to present it graphically. The book then looks at Excel's built in features for financial analysis, and even shows how you can combine the built in features to build your own analysis functions.</p>
Table of Contents (14 chapters)
Excel 2010 Financials Cookbook
Credits
About the Author
About the Reviewers
www.PacktPub.com
Preface
Index

Collecting user sales data across multiple sheets


Financial data such as sales information is often separated into multiple sheets. A common separation would include one worksheet for each day of the week. It is then necessary to calculate a total of sales by individual.

In this recipe, you will learn to use the SUM formula combined with VLOOKUP formulas to gather sales information across multiple sheets.

How to do it...

The sales information in this recipe includes the sales of 10 individuals spread across three worksheets for sales performed on Monday, Tuesday, and Wednesday. Finally, we will calculate the total sales by individual on a Summary tab:

  1. 1. On the Summary page, click on cell D2 and enter the first part of the formula to create the SUM function and the beginning of the VLOOKUP function:

  2. 2. While still entering the formula, click on the Monday tab and select all of the cells from B2 through D11 (this is the full range of data minus the column headings). After selecting the cells...