This lesson deals with some basic data manipulation in Excel.
We will see how to move across cells in a spreadsheet.
For example, how to move from the top of a column to the bottom of that column.
How to format cells.
How to sort various columns in a spreadsheet.
How to add levels to the sorting.
How to hide some columns so that they're not visible, how to unhide them.
Let's continue with a study of basic data manipulation in Excel.
So let's open Excel, and
let's open the Excel data file, Store Sales.
Now how many orders are there in this data file?
Or in other words, how many rows of data are in this file?
So one easy way to find that out is to put your cursor on cell A1 and
then press Ctrl down arrow.
So notice the file has 8,399 orders.
Remember the very first row is a header row.
To go back to the very first cell you press Ctrl and the arrow up.
Takes you to the very first set.
Also notice the sales column.
Some of the sales values have decimal places in excess of two.
For example, that particular value has four decimal places.
So if we wish to organize sales like that we only get to see two decimal places.
So we select the column, you right click, format cells,
number, decimal place is two decimal places and okay.
Now notice the decimal places, all the decimal places have been shorted or
rounded off to two decimal places.
Also the file is not organized in any particular order.
For example, notice the very first order has a 2011 date.
The second order, a 2010 date.
Third order, 2012.
So let's sort this data file in an increasing order
of the date on which the order was received.
Further, on a particular day, the store might have received many orders.
So within that particular day we'll sort it based on the dollar sales amount.
And in particular we'll sort it on a decreasing order of
the dollar sales amount.
So let's first select the entire data file.
One easy way to do that is, put your cursor in the very first cell, cell A1,
press a control, shift, right arrow,
then a control, shift, down arrow, so the entire data file gets selected.
Then, we click on data, Sort.
We wish to sort by the order dates.
So we select sort by order date, and remember the order dates,
the format of dates is month, day, and then year.
The order of sorting, we want the oldest dates first and the newest later.
Then, after that, within a particular date,
we wish to sort based on the sales dollar amount.
So we'll add a level here.
Check on Sales.
And since we wish to order rather than ordering from
smallest to largest we wish to order it largest to smallest.
Then do an okay.
And also notice since my data has headers, I need to make sure that the box
that says my data has headers needs to be checked, and I press okay then.
The file is now all properly sorted, as per an increasing order date.
Within a particular date.
The orders have been sorted in a decreasing order of sales amount.
Now, this data has many columns.
If you wish not to see all the columns, we can hide some of the columns.
For example, let's say if we wish not to see the products subcategory and
the product container columns.
So we can select the two columns, do a right click, click on hide.
So what it does is it hides the two columns.
Column K and column L.
It doesn't delete them, it simply hides them.
If you wish to unhide them, what we do is, we select the two adjacent columns,
do a right click, unhide, so it brings them back into the view.