Organizing and Managing Data in Microsoft® Excel®

[Pages:26]Organizing and Managing Data in Microsoft? Excel?

PARTICIPANT WORKBOOK



All trademarks are the property of their respective owners. SkillPath claims no ownership interest in the trademarks.

? SkillPath?. All rights reserved.

Introduction

Course overview There are infinite ways to store data in Excel?...but if you do it purposefully, you'll have many of opportunities to discover hidden details.

Many workbooks are difficult to manage because their authors didn't understand effective ways to organize data.

In this course, you'll learn how to organize, validate and manipulate data to reveal trends and patterns.

Learning objectives

? Learn how to sort data using conditional formatting ? Discover how custom lists can make your data organization easier ? Learn how to join data from different columns ? Outline spreadsheet data quickly and easily ? Use hyperlinks to connect multiple worksheets, workbooks and websites ? Use filtering on text columns, numerical fields and date fields ? Use Advanced Filter -- a power tool for specialized filtering

Notes to readers Throughout this workbook, you'll see study aids that will help you master Microsoft? Excel?.

? SkillSteps: The fundamental steps to get to a feature ? SkillTips: Special guidelines for becoming a power user ? Strategies: Techniques for mastering Microsoft? Excel?

v737_200109

03 Organizing and Managing Data in Microsoft? Excel?

How to Organize Data in Microsoft? Excel?

There are three guiding principles in Excel?: 1. Typing is trouble. 2. Skipping rows and columns is the road to ruin. 3. Tables are the way to go.

Strategy: When you are inputting information into a spreadsheet, keep in mind...

? Try to keep your data in consecutive rows or columns. Don't skip rows or columns just to make the data

more readable.

? Use one-row titles at the tops of columns. Two-row titles will ultimately be harder to manage. ? Typing is trouble. Design your spreadsheet with this mindset and automate as much as you can by

using formulas. SkillTip: For speed, many power users press CTRL + ENTER rather than ENTER after typing a value or formula into a cell. Workbook problems also stem from an initial workbook design that is not sustainable. For example, to divide your data into separate month-oriented sheets and then consolidating those months onto a Year-to-Date page is a daunting process. Here are some examples of what NOT to do:

On the other hand, it might be easier and more sustainable to store your data on one single sheet. At first glance, this may seem impractical, but as your data grows, it will be much more flexible. You'll be able to easily consolidate, filter and analyze your data in PivotTable reports. (That's if you know about PivotTable reports!) PivotTable reports enable you to arrange your data in effective ways. You can't arrange your data as easily when its separated onto multiple sheets...or in multiple ranges.

04 Organizing and Managing Data in Microsoft? Excel?

v737_200109

An example of "bad organization"

Artificially separating data like this makes it difficult to be agile. You cannot easily build cumulative reports that span the three teams because you've nested the name of the team (Dodgers, Astros, Giants) in the header of the table. Information like this is better stored in the dataset, rather than atop the dataset. An example of "better organization" It's much better to organize your data in a tabular fashion, with formulas and data validations.

SkillSteps: To format a range of cells as a table: Put your cursor in the data > Press CTRL + T

You can see how the Team Table is linked to the Fee Table via a Lookup.

SkillSteps: To trace a formula's precedents: Put your cursor in the formula Cell > Formulas > Formula Auditing > Trace Precedents

v737_200109

05 Organizing and Managing Data in Microsoft? Excel?

Generating visualizations Organized data also makes it easier to generate a dashboard -- a single sheet of charts that communicate the status of your numbers in a single sheet.

A well-designed Excel? workbook often has:

? A single input sheet filled with raw data ? One or more staging sheets with PivotTable reports and formulas ? A single dashboard sheet with charts linked to the reports which are linked to the raw data

Additional checklists

For address lists: Put city, state and ZIP code information in separate columns.

Put first names and last names in separate columns or in reverse order (Smith, Joe) if in the same column.

Enter dates with slashes or hyphens ( 9/15/12, 9/15/2012, 9-15-12).

Don't put multiple dates (like starting and ending dates) in the same cell.

For tables:

Each column should contain a minimal amount of information.

Create a single title row containing unique field names as the top row of the data.

Structure data collections with no empty rows and no empty columns within the data. The list on page 7 is structured to optimize the use of many of Excel?'s data management tools.

Sometimes you must adjust the PivotTable to get the results you need.

06 Organizing and Managing Data in Microsoft? Excel?

v737_200109

How to create a custom group in a PivotTable

The entire list of names might appear uninteresting...until your format it as a table. From there you can perform rapid calculations by grouping different sets of data. Once you've formatted this list as a table and summarized it with a PivotTable, you can group the amounts in ranges of ten.

SkillSteps: To group a range of numbers in a PivotTable: Put your cursor in the data > Press the right mouse button > click Group...

v737_200109

07 Organizing and Managing Data in Microsoft? Excel?

How to create a list A list is a "lightweight" version of a table. It doesn't have all the attributes of a table, but you might have inherited one in an older Excel? file.

SkillSteps: To create a list: Put your cursor in the data > Press CTRL + SHIFT + L On the other hand, by formatting a range of cells as a table, you'll have all the features of a list...and much, much, more.

SkillTip: While you can format a range as a table, you can always convert it back to a range and use the List feature instead.

08 Organizing and Managing Data in Microsoft? Excel?

v737_200109

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download