Title: How to Sort Data in Microsoft Excel
1How 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.
2Once you have data entered into an Excel
worksheet, you can sort it using any field you
would like e.g. Dept, Name, PO, etc.
3Highlight 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.
4A 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.
5The entire Excel worksheet is now sorted by
Name in ascending order.
6To 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.
7If 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.
8The Excel worksheet is now sorted first by Name
and then by Current Bal, both in ascending
order.
9PivotTableS
- 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.
10Once 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
11In 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.)
12A 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.
13By 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.
14To 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.
15Continue 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.
16Moving fields to different areas of the
PivotTable will allow you view information in
various formats. Experiment to find what works
best for you.
17To 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.
18The result is a clean looking worksheet that
contains the data you want in a format that meets
your needs.
19Contact 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.