organizing and managing data in microsoft® excel® · v737_200109 organizing and managing data in...

www.skillpath.com

Organizing and Managing Data in Microsoft® Excel®PARTICIPANT WORKBOOK

Organizing and Managing Data in Microsoft® Excel® 03v737_200109

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®

04 Organizing and Managing Data in Microsoft® Excel® v737_200109

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.


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


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.


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…


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.


When you format a range as a table

• As you scroll down the sheet, the headers will replace the letters above each column of the table.

• You can use the Data Form feature to view your data one record at a time in a pop-up window. (Note: The

table must have no more than 24 columns.)

SkillSteps: To display the Data Form:

Put your cursor in the data > Press ALT, D, O. (Note: Press each key separately — one at a time.)

SkillTip: You can use the Data Form to add, delete and search records.

A table also makes it easy to add “Helper” columns.

Use Helper columns to make it easier to build effective PivotTables. Often, it’s easier to build a PivotTable by

creating formulas in the raw data — rather than in the PivotTable.

For example, if you wanted to group your PivotTable by year or month, it’s easier to write your formula beside the

raw data.

SkillTip: In the TEXT() function, “yyy” is the code for year, “mmm” is the code for month, “ddd” is the code for

day of the week.

Using Filtering on Text Columns, Numerical Fields and Date FieldsBy converting a range of cells to a table, you’ll have many convenient built-in text, numerical and date filters.

There are many filter choices.

SkillTip: The filter choices Excel® provides depend on the format of the columns. Date columns provide date

filters; number columns provide number filters.

SkillTip: You can use the Search (All) window to filter the checkbox list.


Filtering by slicers

A Slicer is another method of filtering a list.

SkillSteps: To filter a list by Slicer:

Put your cursor in the data > Table Design > Tools > Insert Slicer

The benefit of one-row headers

A two-cell header is huge obstacle. You can’t have them in tables. Sorting would be confused by a two-cell

header.

However, there is a workaround…

SkillTip: When you want to put a line break in a cell (to make the heading appear as if it’s two rows), press

ALT + ENTER.


Sorting a list by a custom list

When you need to sort a list by a “non-standard” pattern, use a custom list.

SkillSteps: To create a custom list:

File > Options > Advanced > Edit Custom Lists

There are countless methods to sort and filter your tabular data — depending on the data type.

You can even sort and filter by color!

If you had some rows formatted with a green background, you could sort by that color. However, formatting

individual rows manually is a tedious chore. It would be easier to apply a conditional format…based on a

formula…that automatically painted the cells.


How to Sort Data Using Conditional FormattingConditional formatting enables you to format cells based on their contents.

The conditional format commands provide many features, including:

• Highlight Cell Rules

• Top Bottom Rules

• Data Bars

• Color Scales

• Icon Sets

Conditional formatting is based on TRUE.

In this example, you can write a formula that determines whether the “Rep” is “Chuck.”

If you see TRUE and FALSE, then your formula is working.

You don’t really need the column — but you need the formula.

SkillSteps: To apply conditional formatting with a formula:

Home > Styles > Conditional Formatting > New Rule > Use a Formula…


To sort data that has been conditionally formatted, you need to enable the Sort command and then select the

appropriate column.

SkillTip: When entire rows are conditionally formatted, you can choose any column by which to sort.

Using data validation to make a drop-down list

Keep in mind, you could have put Ryan’s name into the formula…but that would not be optimal

data organization.

You can convert the single cell that contains Ryan’s name into a Data Validation list. This would enable you and

your users to select names from a list that would be passed to the conditional formula.

SkillSteps: The Speed Key combination to create a Data Validation list: ALT, D, L


How Custom Lists Can Make Your Data Organization EasierDo you need to sort data in a specialized way — neither ascending nor descending? A custom list could make

things much easier for you.

SkillSteps: View or create custom lists: File > Options > Advanced > General > Edit Custom Lists

Here’s the classic scenario: You need to sort transactions by the day of the week — Monday through Friday.

However, if you sort the list in ascending order, Friday comes before Monday.

That’s not going to work.

Solution: Sort using a custom list.

SkillTip: The additional benefit of a custom list is that it’s now another “Fill Handle” list that automatically

appears when you drag the Fill Handle of a cell that contains one of the members of the list.


How to Join Data From Different ColumnsHave you ever needed to combine data from separate columns — Last name, “comma space”, First name?

You have many choices. Which one do you prefer?

Ampersand (&)

The ampersand is one way to combine text. It’s often used to combine text strings.

SkillTip: The ampersand can also be used to combine text and numbers.

Consider the formula:

=”The Date Range is From “&TEXT(L11,”mmm d, yyyy”)&” to

“&TEXT(L12,”mmm d, yyyy”)

You would use this in the following scenario to incorporate the date range into the formula for the chart title.


CONCAT

The CONCAT function is another solution.

=CONCAT(F4,” “,G4)

Flash Fill

For what it’s worth, Excel® has a built-in sensor that might fill in sequences or patterns of information.

It’s called Flash Fill, and it uses “artificial intelligence” to predict the remainder of a column based on a sample

of data.

In Excel®, when you begin to type in a column, if Excel® recognizes a pattern, it will suggest the remainder of the

column. You can accept the suggestion or reject it.

SkillTip: If Flash Fill does not fill in a column automatically, you can trigger Flash Fill with CTRL + E.

SkillSteps: To enable or disable Flash Fill:

File > Options > Advanced > Editing Options > Automatically Flash Fill


SkillTip: Flash Fill does not “re-fill” if one of the cells change. If Sheila changes her last name to Jones, Flash Fill

does not update.

Need the last four of the Social? Flash Fill to the rescue

You can use Flash Fill to conceal all but the last four digits of a Social Security Number!

If Flash Fill doesn’t fill the way you want it to, you can always use a Flash Fill “revision.”


TEXTJOIN

Often, users have needed to combine multiple text strings, and were limited to a complicated CONCAT function

or liberal usage of ampersands. If you needed to join text strings from a range of cells, you had to specify each

cell individually.

Compare these two solutions.

The old-fashioned way:

=CONCAT(M3, “; “,M4, “; “,M5,”;

“,M6, “; “,M7, “; “,M8,”; “,M9)

With TEXTJOIN, it’s:

=TEXTJOIN(“; “, TRUE, M3:M9)

SkillTip: The TEXTJOIN function combines the text from multiple ranges and/or strings and includes a delimiter

you specify between each text value that will be combined. It also provides an IgnoreEmpty argument — if

TRUE, and the result is an empty text string, TEXTJOIN skips over the blanks. If the resulting string exceeds 32767

characters (cell limit), TEXTJOIN returns the #VALUE! error.

When you want to use those email addresses you’ve joined, remember it’s still a formula. To use them, you must

paste the joined text “as values.”


Always Use a Table for Chart DataWhen you build charts based on tabular data, the chart changes as the table grows. That’s another compelling

reason to organize your data as tabular.

Outlining Spreadsheet Data Quickly and EasilyOutlining a spreadsheet enables you to easily compress and expand sections of your spreadsheet.

While you can manually define outline levels, it’s faster to use the

Subtotals feature to group your data quickly and easily.

SkillTip: It’s essential to sort your data before inserting subtotals,

because subtotals are defined by the groups of rows.


SkillSteps: To outline a range of cells:

Make sure the range is sorted > Data > Subtotal

After making your choices, you’ll notice the outline

level defined for your data. You can expand or

compress each section by clicking on the outline

level numbers.

To subtotal by Division and Dept., go through the same

steps except:

• Add a summary calculation (Sum) at every

change in Dept.

• Uncheck the “Replace current

subtotals” checkbox.

• Press OK.


Using Hyperlinks to Connect Multiple Worksheets, Workbooks and WebsitesInserting hyperlinks in a workbook enables you to provide navigation shortcuts for your users.

You can hyperlink objects and text to other locations.

SkillTip: Did you need a macro that generates a list of all the worksheets in a workbook?

Sub Sheet_List_Loop()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

ActiveCell.Offset(ws.Index).Value=ws.Name

Nextws

End Sub

You can paste this code on the code page of a worksheet and run the macro to generate a list of every sheet in

your workbook.


Using Advanced Filter — a Power Tool for Specialized FilteringThe Advanced Filter feature enables you to extract rows of information from a data range based on a

certain criterion.

You’ll find the Advanced Filter commands on the Data tab of the ribbon.

SkillSteps: To enable Advanced Filtering: Data:

Sort & Filter > Advanced Filter

The Advanced Filter requires a List range and a Criteria range. You can filter “in place” or copy the rows that

match your criteria to an Output range.

SkillTip: Copy the Column Titles from the List range to your Criteria range and Output range to ensure the

names of each column matches. Note: You don’t have to copy all columns to the Criteria and Output areas.

Once you’ve organized your data extract, you can sort it and filter it further by using conventional methods.


Filtering a Range of CellsYou don’t need a table to filter data. You can always filter a list using the buttons that appear on the header row.

And the Filter feature provides the same choices as those at the top of a table.

skillpath.com

ORGANIZING AND MANAGING DATA IN MICROSOFT® EXCEL® 1/20

organizing and managing data in microsoft® excel® · v737_200109 organizing and managing data in...

Documents