USC Marshall School Of Business Excel 2007 - Charts

1y ago
18 Views
2 Downloads
8.03 MB
58 Pages
Last View : 21d ago
Last Download : 2m ago
Upload by : Isobel Thacker
Transcription

USC Marshall School of BusinessAcademic Information ServicesExcel 2007 - ChartsPie, Column, Bar, Line, Area, Waterfall, XY(Scatter), Bubble, Stock, Pyramid, & RadarGENERAL OVERVIEW OF THE CHARTING ENVIRONMENT . 3Step A:Highlight Your Data . 3Step B:Select the Chart Type . 4Step C:Set Formatting & Layout Options . 5HIGHLIGHTING NON-ADJACENT DATA. 7CHART PLACEMENT AND RESIZING. 8Placing the Chart on a ChartSheet . 8Placing a Chart in Word/PowerPoint . 8PRINTING CHARTS . 9UPDATING CHARTS . 9PIE CHART CREATION . 10Using the Pre-Set Layout and Style Galleries . 11PIE CHART CUSTOMIZATION . 12Label Placement . 12Label Type & Format . 13Label Color, Size, Type Face . 13Change Slice Color. 14Explode a Slice of Pie . 14Rotate the Pie . 14Remove /Display the Legend/Title. 14Resize / Move a Chart within it Boundary . 143-D PIE – CHANGE CHART TYPE . 15Adjusting a 3-D Pie Rotation, Tilt, & Thickness . 15LINKED PIE CHART . 16COLUMN CHARTS . 18Apply a Predefined Layout & Style. 19COLUMN CHART CUSTOMIZATION . 19Title the Horizontal & Vertical Axis . 19Title the Chart . 20Place Column Values on the Columns (Data Labels). 20Adjusting the Legend . 21Gridlines . 21Adjust the Vertical Scale Values . 22Horizontal Axis Labels and Negative Numbers . 22 Marshall School of Business - USC7/2/12Wayne WilmethPage 1 of 58

Changing Column Colors . 23Stacking Pictures Inside of a Column . 23Add a Background Color. 24Column Width & Overlap . 24SWITCH LEGEND AND X-AXIS LABELS – COLUMN/LINE/BAR/AREA CHARTS . 25BAR, AREA, & LINE CHARTS . 26BAR CHARTS – MOVING THE AXIS FOR NEGATIVE NUMBERS . 27MIXED SERIES CHARTS (COLUMN, LINE, BAR, AREA) . 28STACKED CHARTS WITH TOTALS – COLUMN, BAR, LINE, & AREA . 29Showing Totals on a Stacked Chart . 29DISPLAY A RUNNING TOTAL . 31SPECIFY SERIES ORDER – COLUMN, BAR, LINE, & AREA CHARTS . 32ADD A SECONDARY Y-AXIS . 333-D CHARTS - COLUMN, BAR, LINE, & AREA . 35Reverse Series Order. 35Rotating the Chart . 35WATERFALL CHART (STACKED COLUMNS W/CLEAR BARS) . 36XY (SCATTER) CHARTS . 40BUBBLE CHARTS . 43STOCK MARKET CHARTS . 44High Low Close Stock Chart. 44High Low Open Close Stock Chart . 45Volume High Low Close Stock Chart . 46Volume Open High Low Close Stock Chart . 47PYRAMID CHART . 49RADAR CHART (AND VENN DIAGRAM) . 51MANUALLY SETTING THE DATA RANGES . 52USING BLANKS TO CONTROL COLUMN PLACEMENT. 56 Marshall School of Business - USC7/2/12Wayne WilmethPage 2 of 58

GENERAL OVERVIEW OF THE CHARTING ENVIRONMENTThis section will give you a very general overview of the charting environment. Subsequent sections go into detailon how to create and edit specific chart types.Step A:Highlight Your DataFor most charts, you will first highlight your data on your spreadsheet. This tells Excel which data to use whencreating the chart. Note that each chart type requires the data to be laid out in a very specific way. If your data isnot laid out how Excel expects for a specific chart type, you will have to set the series, legend, and axis individually.Some of the common data structures required are shown below. Chart Title(Optional)ValuesdetermineSlice size Slice Labels PIE CHARTSPie Charts require two ranges: Text to name theslices and numbers to determine slide size.Data for a Pie chart can be horizontal or vertical.COLUMN/LINE/AREA/BAR CHARTSThese require at least 3 ranges: one forthe X-Axis, one for the Y-Axis (Series), andone for the legend.LegendLabelsX-AxisLabels1st Series ofColumns2nd Seriesof ColumnsNote that you can swap your X-Axis with yourLegend by click the “Switch Row/Column”button located on the Design tab. This is alsouseful if the data on the sheet is transposed. Marshall School of Business - USC7/2/12Wayne WilmethPage 3 of 58

Step B:Select the Chart TypeNumber driven charts are available under the “Insert” tab.Alternately, click“Create Chart” to seemore charts available.Click a Chart to see its subtypes. After you select a chart, it will appear in Excel. Youcan then set your chart options and formatting. Marshall School of Business - USC7/2/12Wayne WilmethPage 4 of 58

Step C:Set Formatting & Layout OptionsAfter you have created your chart, you can specify such chart options as data labels, colors, etc. When you clicka chart or Chartsheet, the three Chart Tools tabs shown below will appear.a. Click the chart (or Chartsheet) to display the “Chart Tools” tabs.b. There are three Chart Tools tabs: Design, Layout, & FormatDesign Tab – Preset Layout & Formatting CombinationsChange ChartTypeConverts thecurrent chart toa different charttype.Select DataAllows you tochange yourdata ranges.Chart LayoutsUse presetcombinations of labelplacement, gridlines,axis types, etc.Chart StylesClick these to usepreset combinationsof color schemes.Move ChartAllows you toplace the charton a ChartSheetSpreadSheet.Layout Tab – Specific Chart Layout OptionsYou will use the options on this tab to control what items are displayed and where at a more specific level then the“Design” tab allows. Note that grayed out buttons indicate that the option does not apply for the type of chartyou are creating.This section is useful becauseit allows you to select andthen format a specific area ofyour chart by either clickingthe area on the chart orselecting it from the dropdown list. Once the area isspecified, click “FormatSelection” to display optionsfor the selected item. Marshall School of Business - USCAll of your labeloptions are in thissection. Labelsinclude pie slicelabeling, X & Y axislabels, numbersabove, bars or ondata points, as well asthe legend.These optionsallow you toposition,hide/displayyour X & Yaxes and thebackgroundgridlines.7/2/12Wayne WilmethUse theseoptions tohide/displaythechart/floorwalls androtate 3-Dcharts.Use theseoptions to setanalysisoptions oncharts whichhave an X andY axis.Page 5 of 58

Format Tab – Format Specific ObjectsYou will use this tab primarily to change the color of specifically selected items. For example pie slice or bar colors,font color, etc.Font Size/Face/BoldNote that these fontoptions are on the“Home” tab.Shape Effects – Use toadd shadows, glows, etc.to a selected item.Text Fill – Use tochange the color ofselected text.Shape Outline – Use tochange the color of linesand the outlinesurrounding bars, pieslices, lines, etc.Text Outline –Change the color ofthe line around theselected text.Shape Fill – Use tochange the color of theselected area: bars, pieslices, the background,etc.WordArt – Use tocreate unique texteffects.Right Click a Specific ItemNote that right clicking an item will typically display a menuoption specifically designed for the item you right clicked. Marshall School of Business - USC7/2/12Wayne WilmethPage 6 of 58

HIGHLIGHTING NON-ADJACENT DATAAs stated previously, you typically highlight your data and then create your chart. This section shows how tohighlight data that is non-adjacent. The simple trick to it is this:Hold down the CONTROL key on the keyboard to highlight non-adjacent data.Pie ExampleIn this example, we wish to show howGM’s sales have changed over thefour quarters of the year. You wouldhighlight as follows:1. Highlight B4:E4.2. Hold down “Control” on yourkeyboard and highlight B6:E6.3. Click the “Pie” button on the“Insert” tab.Column ExampleIn this example, we wish to compareGM and Toyota sales over the fourquarters as a column chart. Youwould highlight as follows:1. Highlight A4:E4.2. Hold down “Control” on yourkeyboard.3. Highlight A6:E6 and A9:E9.4. Let go of Control.5. Click the “Column” button on the“Insert” tab. Marshall School of Business - USC7/2/12Wayne WilmethPage 7 of 58

CHART PLACEMENT AND RESIZINGExcel will automatically place the chart as a floating object on the same sheet its data is on. If desired, you can movethe chart by clicking and dragging it or placing it on its own sheet known as a “ChartSheet”.Deleting a Floating ChartTo delete a chart, simplyclick its edge and thenpress “Delete” on yourkeyboard.Moving a Floating ChartTo move a chart on thesheet, simply grab itsbackground and drag.Resizing a Floating ChartTo resize a chart on thesheet, grab its corner andclick & drag.Placing the Chart on a ChartSheetIf you would like to move a chart from aspreadsheet to a chartsheet (or vice versa),follow these steps:a.b.c.d.Click the chart.Click the “Design” tab.Click the “Move Chart” button.Specify the location.(New Sheet places it on its own ChartSheetand Object In allows you to place it on aWorksheet.e. Click “OK”.Placing a Chart in Word/PowerPointTo place a chart in Word or PowerPoint, simply copy and paste itthere.a. Right click the chart in Excel and select “Copy”.b. Right click in Word or PowerPoint and select “Paste”. Marshall School of Business - USC7/2/12Wayne WilmethPage 8 of 58

PRINTING CHARTSPrinting your charts isfairly straightforward.Print Spreadsheet & ChartIf you click a cell the chartis on before you print,Excel will print out thespreadsheet data and thechart on the same page(assuming they both fit).Print Chart OnlyIf you click the chart priorto printing, Excel will fillthe printed page with thechart you have selected.UPDATING CHARTSCharts are linked to the data they were created from. If you update the data, the chart will automatically update.This is true for both charts on a chartsheet and a spreadsheet. Note if you delete a chart, the data on thespreadsheet will not delete. Marshall School of Business - USC7/2/12Wayne WilmethPage 9 of 58

PIE CHART CREATIONPie charts are typically useful for showing the parts that make up the whole at a frozen point in time. For example, yourcompany budget by division, population demographics, your total sales by type, etc. When creating a pie chart, youneed to supply two pieces of information minimum: Numbers which will be used to determine the size of each slice,text which will be used to label each slice, and you can also provide an optional title which will be placed above thechart. An example graph and the data grid that created it are shown below.Note that while you can type the values on the grid as eithernumbers or percentages, you can always display numbers aspercentages later. Excel will do the math for you and convertthe numbers into accurate percentages of the whole. (i.e.600 becomes 30%, 400 becomes 20%, etc.) Chart Title(Optional)Slice Labels 1.2.3.4.5.ValuesdetermineSlice size Click the “AutoSales” sheet.Highlight A1:B6.Click the “Insert” tab.Click the “Pie” button.Click the “2-D Pie” subtype.Excel should create a Piechart similar to the oneshown here. Over thenext few pages, we willwork on changing itsformatting. Marshall School of Business - USC7/2/12Wayne WilmethPage 10 of 58

Using the Pre-Set Layout and Style GalleriesThis section will cover how to control labels and colors on your pie using the preset layout/formattingcombinations. This is the fastest way to assign multiple settings to your pie at once although you may not find thecombination you are looking for. In the next section we will cover how to control specific aspects of the chart.1. Click your chart to display the Chart Tools tabs.2. Click the “Design” tab.3. Click the desired Layout or Style. (See below.)Chart LayoutsUse these pre-set options tocontrol the existence andlocation of slice names, values,percents, and the legend.Chart StylesChoose from these pre-set slice colors and bevel effects.More OptionsClick the down arrows to view more options. Marshall School of Business - USC7/2/12Wayne WilmethPage 11 of 58

PIE CHART CUSTOMIZATIONThe previous section covered how to use the pre-set Chart Styles and Chart Layouts to assign multiple settings to our pieat once. This is fine assuming there is a style or layout which has the features you want. This section covers how tocustomize your pie chart by accessing each option individually.1. Click your chart to display the Chart Tools tabs.2. Click the “Layout” tab.Icons that are grayed out are not available for the type of chart you are creating. For example, “3-D Rotation” is onlyavailable for three dimensional charts.This section allows you to control the following:Where data labels appear. (i.e. inside the slices, outside the slices, etc.)Which data labels appear (i.e. numbers only, percents only, slide names only, or any combination of.)The format data labels appear in (i.e. numbers, percents, decimal places, in thousands, millions, etc.)Label PlacementLabels include values, percents, and slice names.1. Click your chart.2. Click the “Data Labels” button3. Select a location form the list.Label PositionUse these optionsto control wherethe labels areplaced.Note that you can also clickand drag labels to placethem.Note that if you have a labelselected, the options abovewill affect just that label. Marshall School of Business - USCMore OptionsClick this to accessall possible labeloptions.7/2/12Wayne WilmethPage 12 of 58

Label Type & FormatThis section covers how to specify which labels appear and how they are formatted.The diagram below shows what effect each checkbox will have on your pie chart. (In reality, you would probably notwant to check all of these. For example, “Series Name” is almost never checked in a pie chart.)1. Click your chart.2. Click the “Data Labels” button3. Click “More Data Label Options”.Alternatively, you can right click one of thelabels and select “Format Data Labels”.Location of thelabels on the pie.How to separate thedifferent labels. (i.e.separate lines,commas, etc.)Label Color, Size, Type FaceFollow the steps below to change the color of your labels.1. Click one of your labels to select them all.2. Click the “Home” tab.3. Use the “Font” tools to modify your labels.Type FaceText SizeText Color Marshall School of Business - USC7/2/12Wayne WilmethPage 13 of 58

Change Slice Color1.2.3.4.Keep clicking the slice whose color you wish to change until dots appear on just that slice.Click the “Format” tab.Click the “Shape Fill” drop down arrow.Select a color.Explode a Slice of Pie1. Keep clicking the slice you wish to emphasize until it is the only piece with dots on2. Click and drag the slice away from the center of the pie.it.Rotate the Pie1. Right click any slice.2. Select “Format Data Series” or “Format Data Point”.3. Adjust the rotation slider and click “Close”.Remove /Display the Legend/TitleIf you don’t want a legend ortitle, simply select it andthen press “Delete” on yourkeyboard.To redisplay a deletedChart Title, click the“Chart Title” drop downunder the “Layout” tab.To redisplay a deletedLegend, click the“Legend” drop downunder the “Layout” tab.Resize / Move a Chart within it BoundaryResize - To resize the overall pie, click the pie to get a frame around it and thendrag one of the corner dots.Move – To move the pie around within its boundary, click the pie to get a framearound it and then drag the pie by the frame (not one of the dots). Marshall School of Business - USC7/2/12Wayne WilmethPage 14 of 58

3-D PIE – CHANGE CHART TYPEIf you would like to create a three dimensional pie, follow the steps in this section.1. Create the pie shown in the previous section.2. Click the “Design” tab.3. Click “Change Chart Type”.4. Click the “Pie” category.5. Click the “Pie in 3-D” sub pie.Adjusting a 3-D Pie Rotation, Tilt, & ThicknessThis section covers how to change the pie thickness and angle.1. Click the “Layout” tab.2. Click the “3-D Rotation” button.X:Spins the pieon its axis.Y:Tilts the pie.Height (Thickness)Use this to control thepie thickness. Notethat “Autoscale” mustbe unchecked to accessthis option. Marshall School of Business - USC7/2/12Wayne WilmethPage 15 of 58

LINKED PIE CHARTA Linked Pie chart is usefulwhen you wish to show an indepth analysis of a specific sliceof pie.In the example to the right, thepie shows a breakdown of thecompany budget. TheMarketing slice is furtherbroken down as a column whichshows that marketing expensesconsist of TV, Print, and Radioadvertisements.Linked Pie SetupThe data for a linked pie is setup as follows:Type the labels and values for every slice and barsegment except the slice you are doing abreakdown of. For example, we are doing abreakdown of the Marketing slice so we will leavethe Marketing slice off of our grid.Place the labels/values for the items which will goon the column segments at the bottom of the list.In this example, TV, Print, and Radio are in thebreakdown so they will go at the bottom of the list.These slices go on the main pie. Note that“Marketing” was left off.Items which go on the column as segmentsmust be placed at the bottom of the list.1.2.3.4.5.Click the “Linked Pie” sheet.Highlight A2:B8.Click the “Insert” tab.Click the “Pie” button.Click the “Bar of Pie” subtype. Marshall School of Business - USC7/2/12Wayne WilmethPage 16 of 58

Specifying the Bar LabelsAt this point, your pie probably resembles somethinglike the one shown to the right. We will now specifywhich labels go on the pie and which go on the bar.1. Right click any slice and select “Format Data Series”.2. Set Split Series By to “Position”.3. Set Second plot contains the last valuesto “3”.(This tells PowerPoint that the last threeitems on our grid are to go on the column).4. Adjust the gap width as desired. This is theamount of space between the Pie and theColumn.5. Adjust the Second Plot Size as desired. Thisis how large the column is in comparison tothe pie.6. Click “Close”.Rename “Other” to “Marketing” and Adjust the Formatting1. Highlight the word “Other”.2. Type “Marketing” over it.3. Click the Marketing slice so it alonehas dots on it.4. Drag it away from the center of thepie. Marshall School of Business - USC7/2/12Wayne WilmethPage 17 of 58

COLUMN CHARTSColumn Charts are useful for showing howvalues change over time. For example, howtemperatures are increasing over the last100 years or how sales fluctuate with thepassing seasons in a year.To create this type of chart, you need tospecify the following:X-Axis labels – These will be placedalong the bottom of the columns (i.e.the seasons).Legend Labels – This tells you what thecolumns represent (i.e. Sodas & Coffee).Series Values –These must be numbers.They dictate how high to make thecolumns. (Each item is known as aseries. In this example there is a Sodaseries and a Coffee series.)LegendLabelsX-AxisLabelsNote that the Y-axis scale is createdautomatically based on your series values.1.2.3.4.5.1st Series ofColumns2nd Seriesof ColumnsClick the “Column Chart” sheet.Highlight A1:C5.Click the “Insert” tab.Click the “Column” button.Click the “Clustered Column” subtype.Your chart should resemble the one shown below. Marshall School of Business - USC7/2/12Wayne WilmethPage 18 of 58

Apply a Predefined Layout & StylePrior to getting into the individual settings, you might try some of the predefined layout & style settings.1. Click the “Design” tab.Click these to see if they have any of thelayout options you are after.Click these to change how the chartlooks cosmetically.COLUMN CHART CUSTOMIZATIONThe predefined layouts and styles are great when they contain the combination of options you need; however, whenthey do not, you can customize your chart as desired using the settings covered in this section.Title the Horizontal & Vertical AxisIt is always a good idea to give your Horizontal and Vertical axis a title so your audience has a better understandingabout the data.1. Click your chart to display the Chart Toolstabs.2. Click the “Layout” tab.3. Click “Axis Titles”.4. Click “Primary Horizontal Axis Title”.5. Select “Title Below Axis”.6. Click “Axis Titles” again.7. Click “Primary Vertical Axis Title”.8. Select “Rotated Title”.9. Click within the “AxisTitle” text boxeswhich appear andtype your titles. Marshall School of Business - USC7/2/12Wayne WilmethPage 19 of 58

Title the ChartIf the title outside of your chart area is notsufficiently titling your chart, you can add a titlewithin the chart as well.1. Click your chart to display the Chart Toolstabs.2. Click the “Layout” tab.3. Click the “Chart” title button.4. Select “Above the Chart”.5. Click in the chart title text box whichappears and type a title for your chart.Note that you can drag the title to move it toanother location if desired.Place Column Values on the Columns (Data Labels)This is useful when your audience is concerned with the exact value the height of a bar represents.1.2.3.4.Click your chart to display the Chart Tools tabs.Click the “Layout” tab.Click the “Data Labels” drop down.Select a label position. Marshall School of Business - USC7/2/12Wayne WilmethPage 20 of 58

Adjusting the LegendIf your legend is not displaying, you can make it appear and position it by following these steps:1. Click your chart to display the Chart Tools tabs.2. Click the “Layout” tab.3. Click the “Legend” drop down.4. Select a position.Move the LegendYou can drag your legendanywhere you want bygrabbing it by the framearound it.Reshape the LegendYou can reshapeyour legend bygrabbing one of thecorner dots on theframe.GridlinesGridlines can be both a help and a hindrance to the clarity of your chart. On onehand they can help the eye see how high up a chart travels if you are not usingdata labels but on the other hand, they can make a chart seem cluttered.1.2.3.4.5.Click your chart to display the Chart Tools tabs.Click the “Layout” tab.Click the “Gridlines” button.Select either “Primary Horizontal Gridlines” or “Primary Vertical Gridlines”.Select an option.Major Horizontal GridlinesThe image to the right shows a chart with “Major” horizontalgridlines. A single line will appear for each number on your scale.Minor Horizontal Gridlines (Not Shown)These will give you multiple grid

After you have created your chart, you can specify such chart options as data labels, colors, etc. When you click a chart or Chartsheet, the three Chart Tools tabs shown below will appear. a. Click the chart (or Chartsheet) to display the "Chart Tools" tabs. b. There are three Chart Tools tabs: Design, Layout, & Format

Related Documents:

USC Thornton School of Music Elizabeth M. Daley USC School of Cinematic Arts Gerald C. Davison USC Davis School of Gerontology James G. Ellis USC Marshall School of Business Marilyn L. Flynn USC School of Social Work Karen Symms Gallagher USC Rossier School of Education Howard Gillman USC Dor

USC Marshall School of Business wilmeth@usc.edu Custom_Functions.docx 10/23/2013 Page 1 of 65 USC Marshall School of Business Excel Marshall Information Services Custom Functions The purpose of a Custom Function is to save you time. Just as Excel's built-in functions (sum, average, etc.) save you time by allowing you to plug in values while .

account @ https://you.usc.edu/login/ This service gives you access to your: a) University Decision Letter b) Welcome to USC brochure c) If admitted, Certification of Enrollment Contact Information Office of Graduate Admission gradadm@usc.edu MSF Program ms.fin@marshall.usc.edu Immigration (I-20 and visa questions) usci20@usc.edu .

jcbrown@usc.edu 213-740-7121. emeriti.usc.edu Office of the Vice Provost for Faculty Affairs Debbie Jones. Faculty Retirement Navigator. jonesdeb@usc.edu 213-740-7641. faculty.usc.edu USC Office of Benefits Administration Keri Marroquin. Benefits Retirement Navigator. kerimarr@usc.edu 213-821-8100.

reach: the Trojan Family, with more than 335,000 USC alumni worldwide, and the Marshall Trojan Family, with 80,000 graduates in 123 countries. Location Based in Los Angeles, Marshall students gain unique business and international perspectives. Within L.A. you will find: Headquarters of more than 15 Fortune 500 companies

Through Grandpa's Eyes Maclachlan What You Know First Maclachlan Author Study - Marshall, James 69 Grade: 2 George and Martha Marshall George and Martha Back In Town Marshall George and Martha Encore Marshall George and Martha One Fine Day Marshall George and Martha Rise and Shine Marshall George and Martha Round and Round Marshall

USC - Marshal School of Business AKINDI_v2.docx 5/29/2014 1 of 7 USC Marshall School of Business Customer Technology Services AKINDI Test Forms Creating and Grading Test Forms You can use AKINDI to create multiple choice test forms that can be printed out for student test taking.

University of Southern California Marshall School of Business Lync_IM_and_Presence_v3.docx 7/16/12 Page 1 of 12 USC Marshall School of Business Academic Information Services Microsoft Lync 2010 IM,PC to PC Calling, and Presence Microsoft Lync is a communications system that includes ins