Microsoft excel help

Question

You have been tasked with analyzing an extremely large amount of data and to ultimately produce a report to share with the Board of Directors. The data is currently in a text file and has over two thousand records of data. Explain how you would use Excel to analyze this data and organize it to prepare a written report. Be very specific on the variety of tools you would use and the steps you would go through to analyze the data and to ultimately prepare a detailed report with recommendations.

Answer 1

First, make sure the text file contains comma-separated values; this means, for each record (row of data), the values are separated by commas. For example:

 

First Name,Last Name,Age

John,Doe,32

Mary,Thomas,16

 

Next, follow the below steps to convert the text document to an Excel workbook

 

1. Launch Excel and open the text file

2. Follow the prompts in the Text Import Wizard

3. Save the document as an Excel file

 

ANALYZE THE DATA

 

Analyzing the data can take on many forms; it depends on the nature of the data and the questions that the data must answer. Most analysis starts with calculations using formulas and functions. Which formulas or functions are needed again depends on the nature of the data and the questions that need to be answered. Other analysis tools include sorting, filtering, pivot tables, subtotals, and goal seek.

 

PREPARE A WRITTEN REPORT

 

First, determine if your audience is interested in the details or if they prefer just a high level summary of the data.

 

If details are wanted, preparing a report would involve sorting or filter the records to group like-data together, inserting calculations at the end of each group, then formatting the worksheet with borders, fill colors, font-styles, page layout, etc. to make the document visually aesthetic and easy to follow.

 

If your audience is interested in only high-level summaries, start by creating a new spreadsheet and set it up to present the totals, averages, and summaries of the data in individual cells. Use formulas and functions to refer to the data sheet and make calculations.

 

Finally, data is easier to discern when it is illustrated. Use pie charts or bar graphs to represent the data visually.

 

Answer 2

Yes, this is a question that was answered a ways back, but I feel the need to respond as data analysis and reporting are what I did professionally for many years.  My spreadsheet organization is what set me apart from everyone else in the company's IT organization.

 

Tab 1 - put the untouched data here (usually straight from a .csv file),

however many thousands of rows there are

Tab 2 - format the data from Tab 1 as needed, maybe even perform

some minor conversions (e.g. miles to feet, gallons to ounces, etc.)

Tab 3 - this is where the heavy lifting occurs.  calculations involving

Excel formulas occur here using Tab 2 data.

Tab 4 - organize the resulting data - grouping and sorting happen

here.  this is where your macros get involved (to select and sort

data from Tab 3)

Tab 5 - charts get inserted here by high-lighting data groups from Tab 4,

clicking Insert Chart, then selecting the option that will put the

chart in a new worksheet (Tab 5)