How to Sort Data in Microsoft Excel

1
How to Sort Data in Microsoft Excel

• Microsoft Excels data sort function provides a
  way to prioritize and rearrange data in a
  worksheet.
• In this presentation you will learn to sort data
  in an Excel worksheet and to create a simple
  PivotTable.
• Please feel free to ask questions at any time.

2
Once you have data entered into an Excel
worksheet, you can sort it using any field you
would like e.g. Dept, Name, PO, etc.
3
Highlight the entire document by clicking the
blank box in the upper left hand corner of the
worksheet. Next, click on Data in the toolbar
at the top of the page, then click on Sort.
4
A dialog box will appear, and from the pull down
menu underneath Sort by choose the variable or
column you wish to sort by. The sort order
defaults to Ascending, as in ABC or 123. You
can change this to Descending so that your data
sorts in reverse, like CBA or 321.
Near the bottom of the dialog box you must choose
Header row or No header row. If your
document uses row 1 as a row that lists what the
variables are, like Name and Date, select Header
row. If your document uses row 1 to start the
listing of your actual data, select No header
row. Click on OK.
5
The entire Excel worksheet is now sorted by
Name in ascending order.
6
To view the worksheet by Name in descending
order, just click again on Data, then Sort,
and select the radio button next to Descending.
Click on OK and the data will come up in
reverse order.
7
If you would like to include a secondary sort,
click on the pull down menu underneath Then by
and select the variable or column you want to
use. Click on OK.
8
The Excel worksheet is now sorted first by Name
and then by Current Bal, both in ascending
order.
9
PivotTableS

• PivotTables allow you to create multidimensional
  data views by dragging and dropping column
  headings to move data around.
• With PivotTables, you can quickly turn rows of
  data into summary reports. Once you have the
  summary, you can then change the format to create
  other reports or summaries.

10
Once you have your data entered into an Excel
worksheet, click on any data cell in the body of
the worksheet, click on Data in the toolbar,
and then click on PivotTable and PivotChart
Report.
Data cells
11
In the PivotTable and PivotChart Wizard dialog
box, the radio buttons next to Microsoft Office
Excel list or database and PivotTable should
be selected. Click on Finish. (Clicking on
Next will take you through advanced set-up
options.)
12
A new page will open in your Excel workbook and a
blank PivotTable will appear with a list of
fields from your original worksheet. By dragging
fields from the PivotTable Fields List into
different areas of the blank PivotTable, you can
analyze data in a variety of ways.
Page Fields
Column Fields
Row Fields
Data Items
From a shared master query or worksheet many
users can view the data they need in the format
that is most useful to them.
13
By dragging the Buyer field from the
PivotTable Field List into the PivotTable Page
Field you can view information for one or all of
the buyers.
14
To show a list of all the purchase orders (POs)
processed by the buyer identified in the page
field, click on PO in the PivotTable Field
List and drag it to the PivotTable Row Field
area. If you change your mind, you can drag
fields into different areas of the PivotTable or
back to the PivotTable Field List.
15
Continue dragging items from the PivotTable
Field List to the PivotTable, or moving the
items to different areas of the PivotTable. You
can use as many or as few of the fields as you
like. In the table below, Original Amt and
Current Balance were dragged into the Row
Field where each line value shows separately.
Row Fields
To see the sum of all the lines of a PO, move the
Original Amt and Current Balance fields to
the Data Items area of the PivotTable.
16
Moving fields to different areas of the
PivotTable will allow you view information in
various formats. Experiment to find what works
best for you.
17
To help clean up your tables appearance, summary
lines that appear for each Row Field may be
deleted by placing the cursor on the field to be
removed, clicking on the PivotTable dropdown,
and un-checking Subtotals.
18
The result is a clean looking worksheet that
contains the data you want in a format that meets
your needs.
19
Contact Information

• If you have any questions please contact Joyce
  Baltierra at extension 3612 or by email at
  jlb7002_at_humboldt.edu.
• Please check our website for additional training
  information at http//www.humboldt.edu/procure/t
  raining_guides.htm
• Suggestions for future training are welcome and
  may be emailed to Joyce.