Microsoft Excel: Sorting and Aligning Data

Name: Microsoft Excel: Sorting and Aligning Data
Author: Bob Akbar

Perform proper sorting and alignment of data in tables

Preview the first lesson free — get full access to all 3 lessons.

Course: On-Demand

Hello, my name is Bob Akbar, and in these lessons you will learn how to sort data in table, get data columns ready for charts, and common alignments and data types. Let's start off with our data table, and we're going to highlight it, and we're going to go to data. We are not going to use the two sorts that you see here, A-Z and Z-A, as they will throw off our data. We're going to use our sort table here, and that's why you see there's a table and it says sort. Now, it will allow us to choose multiple values. So the first thing that we want to do is we want to sort this table by state, and we are going to sort on cell values, and we're going to do it alphabetically from A-Z. Next, we're going to add another level to this table and we're going to put employee, and we're also going to do that alphabetically the same way. Now you notice the problem is now we have funny looking values, like zeros, in our states. So let's use our control + Z to undo, we can also use the choice up here, and let's see what happens. Before we can sort the table, we need to make sure our references are static. In other words, the dollar sign, A$16 keeps the value. So John is A16 here. You notice the other one does not have that? So what happens is when it resorts, the reference is shifted. So first, let's make sure that all of our values are static, not dynamic. And as you see, I'm simply putting in dollar signs to where the existing sale values were, so they do not shift, so the employees name stays fixed to the reference that we want. Next we also have the same issue with our states. Our states are also linked to the table, master information, down here. So we have to do the same thing for the references for our state. Now, as you will guess, we have the same problem with our months, and the first one has been fixed, the second one, the third one, the fourth one, and so forth. We're just gonna double check them, and look at that, there is one missing. Always check your data as we're doing to make sure that the static references or fields that you're referencing are setup. Now that we've done that, we can highlight our table, go to data, use our table sorting, not the column sorting, and here we are. Now we can sort by the state, and we can add a level and sort it by the month. And there we are. As you can see, we have California, March, for Florida, which is the next alphabetical state, we have properly alphabetized April, February, and January. Now what if we want to change this? Let's highlight this and let's go back to our sort. And there they are. What if I want to do by employee and I want to keep it alphabetized, and next I want to do by the state? This simple, we can go in there, we can change it. And if I decide I do not even want the level of the state, delete levele is up here. I can get rid of that and simply sort it by employee. And there we are. Stay tuned for the next lesson where I will show you how to prepare your data columns to get them ready for charts. Thanks for watching.

Intermediate Provider : Bob Akbar 3 Lessons · 13m in Arabic, German, English, Spanish, French, Portuguese, Chinese

Course Description

There is nothing more frustrating than having all your data in a table and ready to be presented when a simple sort destroys all relationships between your data in a table. This has happened to users, most often at the beginner level. Why does this happen? How do we prevent it from occurring? These issues are explored and explained in easy-to-understand multiple example layouts. In these lessons, your stress will be reduced by gaining an understanding of why we use sorting, which sorting option is the proper method, and when it is the proper time to sort your data in a table.

By properly preparing the references in a cell, the main list and range options are properly sealed to maintain proper relationships between the data and the integrity of the data table. These operations are crucial to keeping the data integrity when a sort is performed. Also, different data types present different alignments. The differences of some common similar types are explored, and useful instruction is given to align your data.

What You'll Learn

Understand when and how to use sorting for data tables in Microsoft Excel
Recognize the proper sorting option and the right time to sort data in a table
Prepare cell references so the main list and range options are sealed to maintain data relationships and integrity
Sort data within a table while keeping the relationships between data intact
Align common similar data types correctly based on their differences
Explain common alignments and recognize the proper use of each data type

Key Takeaways

A simple sort can destroy the relationships between data in a table, an issue that most often happens to beginner-level users.
Understanding why sorting is used, which sorting option is proper, and when to sort reduces the stress and errors involved in working with data tables.
Properly preparing the references in a cell seals the main list and range options so the relationships between data and the integrity of the data table are maintained when a sort is performed.
Different data types present different alignments, and similar types have differences that affect how data should be aligned.

Frequently Asked Questions

Who is this course for?

It is aimed at users who work with data in Excel tables, including beginner-level users, since losing data relationships after a sort happens most often at the beginner level.

What does this course cover?

It explores why sorting can destroy relationships between data in a table and how to prevent it, when and how to use sorting for data tables, properly preparing cell references to seal the main list and range options, and aligning common data types.

What skills will I gain?

You will work with Microsoft Excel, sorting, spreadsheets, data manipulation, tabular data, and cell sorting.

What lessons are included?

The course includes three lessons: Sort Data in Table, Data Column in Chart, and Common Alignment with Data Types.

What will I be able to do after this course?

You will understand when and how to use sorting for data tables, recognize the proper use of each data type, and explain common alignments.