Excel assignment

ZIDAN

2._advanced_excel_-_chart_assignment.pdf

Home >English homework help >Excel assignment

MICROSOFT EXCEL ADVANCED OPERATIONS CET-10501

LESSON OBJECTIVES  Data Tab

 Sorting  AutoFilter

 Tables  Charts & Graphs  Good & Bad Chart Designs  Assignment

DATA TAB 3

DATA TAB  The Data tab includes:

 Get External Data  Connections  Sort and Filter  Data Tools  Outline

SORTING  Sorting can be done on a single item or on multiple items

 A single-key sort considers only one item when sorting. For example, you may sort a list of students

alphabetically  A multiple-key sort considers first one item, then a

second, etc. For example, you may sort a list of students based

upon grade, then sort the data alphabetically. The result will be that all students that earned an

A would be sorted alphabetically, then all of the B students, etc.

SORTING  Select the range of cells to sort.  Data tab -> Sort & Filter panel -> Sort

FILTER  Select the range of cells to apply the filter to  Use the filter tool

FILTER  Once Filter is selected, click

on the carat down arrow for the additional options:

AUTOFILTER  AutoFilter appears in the

header row of a column to which a filter or sort has been applied  AutoFilter has many options

like (All), (Top10),(Custom).  If a list is previously filtered,

the (All) option is used to bring back the records.

AUTOFILTER  The Top 10 option allows to

filter the top ten items of the list.

 The Top 10 AutoFilter is flexible.  You can choose any number of

items, filter by number or by percentage, or take them off the top of the list or the bottom.

 This works with data only and not text. 10

AUTOFILTER  Greater than  Between  Etc

CUSTOMIZING AUTOFILTER  Select criteria for multiple fields as desired

 Excel displays results that match all of the filters only

ADVANCED FILTER  Advanced Filter feature

enables you to select using multiple criteria for the same field and to make selections based on complex formulae.  Select the range of cells you

need to filter.  Click on the Advanced icon in

the Sort & Filter group

ADVANCED FILTER

 The Advanced Filter dialog box will be displayed.

 The List range gets automatically filled by Excel

Select the Copy to another location and specify the location in Copy to:

ADVANCED FILTER  Select the Criteria range by typing the range or

by clicking on the small rectangle box on the right of text box and drag the mouse on the criteria range to select it.

 Copy to text box is the location where Excel puts the Advanced Filter result.

 Click on the OK button or press Enter.

ADVANCED FILTER EXAMPLE

Original list Criteria#1Criteria#2 Result set of Criteria 1

Result set of Criteria 2

TABLES 18

RANGE  A range of cells is a group of cells that sit

together physically on the spreadsheet  Are defined by the beginning and ending cells

separated by a colon  Capitalization of letters is not necessary

19 Range: a1:a10

Range: d1:f1

Range: e7:g9

CONVERTING RANGES INTO TABLES  Select a range of cells  Insert Tab -> Tables panel -> Table

CONVERTING RANGES INTO TABLES

CONVERTING RANGES INTO TABLES  Additional tools and options with data as a

Table, instead of a raw data Range, including column AutoFilters

 Once you have converted a range into a table you may convert it back to a range of cells by Design tab > Tools > Convert to Range 22

CHARTS AND GRAPHS 23

EXCEL CHARTS  Used as a visual aid for numerical information

 Excel allows you to create a variety of types of charts,  Bar charts, pie charts, line charts, stock

charts, high-low-close charts, etc.

 You can copy charts into Word, PowerPoint, etc.

EXCEL CHARTS  Charts and graphs provide

a visual means of interpreting data.

 Excel is good at producing useful charts. While you can usually get the type of chart that you need, you generally have to modify the appearance of a chart produced by Excel to meet even minimal design criteria. Fortunately, Excel provides the tools to make the needed modifications.

EXCEL CHARTS

 There are three general designs  Bar charts

Excel calls vertical bar charts “Column Charts”

 Pie charts  XY graphs or

Line graphs

BAR CHARTS  A bar chart requires that you have your data

arranged in two columns. The column on the left holds the "category" values and should have the name of each bar. These names will appear at the base of each bar.

 The column on the right has the values. This will be the height of the bar.

BAR CHART PAIRS  A related design is to have pairs of bars, each

relating to a category.  In this example, the values in Set 1 are shown in green

and those of Set 2 are shown in yellow.  The members of each set (that is, a green and orange

pair) are arranged next to each other.

PIE CHARTS  Data for a pie chart are arranged like those of a

bar chart with a single set of bars.

 Although your pie chart will show relationships such as percentages, you should have the actual data values in your worksheet. Excel will calculate the percentages automatically.

XY OR SCATTER GRAPHS  With an XY graph, you arrange the values with

the related pairs as adjacent columns.

DEFINITIONS  Chart Area –

 the background of the chart  Y-Axis –

 the vertical axis of a chart  X-Axis –

 the horizontal axis of a chart  Z-Axis –

 Used in a 3-D chart

ALL BA F IR S UA WY

Currently Own

DEFINITIONS  Plot Area –

 area where bars, columns, or dots show value of x and y axis

 Data Range –  area of spreadsheet with data to be plotted

 Data Series –  individual ranges inside the data range to be plotted

 Data Table –  table showing legend (optional) and the actual data

amount  Category Labels –

 like data labels 32

CREATING A CHART  Insert tab -> Charts panel

 Either select an icon, or select the drop down of Charts panel

CHOOSE CHART TYPE (TYPICAL USES)  Bar charts compare categories.

 This is a very commonly used chart design and the one that you are most likely to need.

 Pie charts show proportions.  If your data add up to 100%, for example, a pie chart

may be best to show the relationships.

 Paired variables, such as a father's height and his son's height, work well with an XY graph.  That's the design that shows the relationship (or

correlation) between the two variables. 34

ARRANGING DATA FOR CHART TYPES

SELECTING DATA  The first step in creating a chart is to select the

data you want to graph.  Select both the numeric data and adjacent row

and column labels; Excel uses the labels for legend and axis information.

 If the data is not contiguous, use Excel’s multiple selection technique; select the first range of data, hold down the Control key then select the second range of data.

 Insert tab > Chart panel

0.0 1.0 2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0

10.0

Michelle Tom Adrian Tony Jonas Mike

MODIFY THE STANDARD DESIGN  The initial result of the insert > chart tool may

not be what you expected or desired.  You may need to modify the initial design.

MODIFYING CHARTS  “Hover” over the element to be edited  Click with the right mouse button. This brings

up a context sensitive menu. This will let you either modify the element or delete it.

380.0 1.0 2.0 3.0 4.0 5.0 6.0 7.0 8.0 9.0

10.0

Michelle Tom Adrian Tony Jonas Mike

Add a legend

Add axis labels

Add a title

INITIAL CHART – WITHOUT MODIFICATIONS  This chart is the initial “automatic” chart. Some

modifications will be required  Axis labels and units  Title  Background  Legend  Bar Widths

SAME CHART – WITH MODIFICATIONS  This is the modified chart.  It more accurately conveys the intended

information.

MODIFYING CHARTS  How do you know which changes to make?

 It starts out with your assuming that Excel will not produce a perfect chart. With that attitude, you can start looking for flaws.

 Some of the needed changes are just common sense. For example, there was a legend to the bar colors, but there was only one set of bars. Therefore, it wasn't telling us anything. It was deleted.

BAD CHART DESIGNS – 3D

 One of the worst designs is the 3D bar chart.

 The purpose of a bar chart is to compare heights of the bars. This is very hard to do with the 3D effect

 Avoid making 3D bars.

 Think: Is it easy to read?

BAD CHART DESIGN – NARROW BARS

 You may need to make your bars wider.

 This is mostly for visual reasons. Narrow bars give the impression that they may fall over or break, while wide bars look substantial.

 Think: Is it visually pleasing?

BAD CHART DESIGN – TOO MUCH INFO

 If you have too many data categories your bar chart will have too many bars.

 These are generally hard to read and interpret.

 There is occasionally a use for lots of bars like this.

 Generally, limit the number of bars to a dozen or fewer.

 Think: Is this legible? Does this easily convey my intent?

ENHANCEMENTS: BAR CHARTS  You should also make changes that emphasize your data.  Example:

 Important bars had their colors changed.  A callout added as a label.  A reference line was drawn

ENHANCEMENTS: INSERTING CALL OUTS  To make visual changes:

 Insert > Illustrations > Shapes  Insert > Text > Text Box

BAD CHART DESIGN: PIE CHART EXAMPLE  Bad color choices for the slices.

 It is hard to relate the legend to the slices.  Too many categories, too many slices.

 The categories with the small values get completely lost.

ENHANCEMENTS: PIE CHART EXAMPLE

 The colors all come from the same general color family so they work well together.

 They are arranged from dark to light.

 The slices have been arranged by their relative sizes.

 Labels are used next to the slices instead of a legend.  The percentage is included with the label so you know

the size of the slice.  One of the slices has been pulled out for emphasis.

BAD CHART DESIGN: XY GRAPH EXAMPLE

ENHANCEMENTS: XY GRAPH EXAMPLE

 Corrections made per previous slide

 Additional Enhancement: There is a trend line drawn through the data points to emphasize the relationship.  Since this line was fit

statistically, the statistical relationship has also been added to the chart in the lower right corner.

 There is a menu item that helps you do this statistical analysis and add the information to the chart.

SOME EXAMPLE CHARTS  Look at the following charts. How can they be

improved?

PERSONAL BUDGET Budget Projections for the year ending 1996 appears below:

METEOROLOGICAL DATA

ASSIGNMENT: CREATING CHARTS Due: 5pm of next class55

ASSIGNMENT  Create two vertical bar charts from the data in

Excel-Chart-Homework.xls  Bar Chart 1: Quiz Average by Student  Bar Chart 2: Quiz Average by Quiz Number

 (there were 10 quizzes)  Red cells on “Quiz Values” tab are data you need

to calculate  (Hint: the formula for M3 is “=AVERAGE(B3:K3)”

ASSIGNMENT: VISUALLY PLEASING  Don’t create a Bad Chart (per today’s lecture)  Include Chart Title  Legend to match context  Included data labels on the table

ASSIGNMENT: PRINTING  Print each chart on its on tab (you will turn in

two pages)  Print on portrait orientation, letter sized paper  Format the header to be right justified, in the top

right corner  Your name  Professor’s Name  Class Name  Date

 Save the spreadsheet to your flash drive or U drive for future use