the Format Data Series dialog box. In
`9. Select the first data series and access
`to None and Area to None. This makes the first
`the Patterns
`tab, set Border
`data series
`invisible.
`10. Apply other formatting,
`
`as desired.
`
`Comparative Histograms
`that you may have considered
`With a bit of creativity, you can create charts
`impossible with Excel. For example, Figure Hi-35 shows data that was used to
`create
`the comparative
`histogram
`chart shown
`in Figure 16-36. Such charts often
`display population data.
`
`Figure 16-35: Data used in the comparative
`h istog ram cha rt.
`
`Prooucr usage oy Age ,met sex
`
`Figure 16-36: Producing this comparative histogram chart
`requires a few tricks.
`
`emnosUSA0017059
`
`CiM Ex. 1053 Page 1
`
`

`
`shown in Figure
`entered as r1egaUve values.
`labeled Clustered Bar.
`the subtype
`Select A1:C8 and create
`2Dhar chart.
`Apply the· fotlowing ·custom• number Io rmat to the horizontal axis: 0%;0%;0%.
`the negative signs in the percentages.
`This custom format eliminates
`and a(:cess the F(;flnat Axis dialog bo,cClk·k
`4. Select the vertfoalaxis
`the
`Patterns tab arid remove au tick marks. Set the Tick mark labels option to
`the chart bufdispl<1.ys the axis Ia.bels
`Low. This keeps the a.xi.sin the center
`at the left side.
`Select either of
`data sei'ies and then .ac(:ess the format Data Series dialog
`box.>Clkk the Options
`tab
`l:UJ.d set the Overlap
`to 100 and the Gap width to 0.
`A('.ld twc;, text bo:x:es t<) the <:hart (Female, i:md Mafos),to sub$titute
`legend.
`
`emnosUSA0017060
`
`CiM Ex. 1053 Page 2
`
`

`
`in row I, the formula subtracts
`
`I
`
`column. Because column A has a heading
`from the number.
`4. Type Sales in the Names in workbook
`field, and in the Refers to field enter:
`=OFFSET(Sheetl!$B$2,0,0,COUNTA(Sheetl!$B:$B)-l)
`5. Click Add and then OK to close the dialog box.
`6. Activate
`the chai·t and select the data series. In this example,
`the formula bar will read:
`Sheetl!$B$2:$B$l0,ll
`=SERIES(Sheetl!$B$l,Sheetl!$A$2:SA$l0,
`7. Replace the range references with the names that you defined in Steps 2 and
`4. The formula should read:
`=SERIES(,Sheetl!Date,Sheetl!Sales,l)
`
`the formula in
`
`Figure 16-37: This chart is updated automatically whenever you add new data to
`columns A and B.
`
`these steps, when you add data to columns A and B, the chart
`After you perform
`will be updated automatically
`to show the new data.
`
`for your o,vn data, make sure that the first argument
`To use this technique
`for the
`0 FFSET function refers to the first data point, and that the argument
`for COIJtH A
`refers to the entire column of data. Also, if the columns used for the data contain
`any other entries, COU in A will retun1 an incorrect value.
`
`emnosUSA0017061
`
`CiM Ex. 1053 Page 3
`
`

`
`off by discuss.ing most of the chart
`This <:hapter picks up wher(:) Chapter
`in Excel. This chapter<demons{rntes how fo
`options
`cusfomization
`cOrnbinatJon drnrbii and your ()Wff custo-rn clrnrt formats..;....;Whkh
`comman& This chapt~l'
`series of c:ustmnizations with a
`charts, and cont:fodes with
`that. use tha:rt~making
`
`tricks.
`
`emnosUSA0017062
`
`CiM Ex. 1053 Page 4
`
`

`
`Creating Maps
`with Microsoft
`Map
`
`In previous chapters. you saw how you can use a chart
`
`usually, more
`display data in a different-and,
`meaningful-way.
`This chapter explores
`the topic of mapping
`and describes how to present geographic
`information
`in the
`form of a map.
`
`to
`
`is not actually part of Excel. Rathei·, this
`feature
`The mapping
`feature uses an OLE server application named Micmsoft Map,
`which was developed by Map Info Corporation. You can use
`this application
`to insert maps into other Microsoft Office
`applications. Because
`the mapping application
`is not part of
`Excel, you'll find that the user interface
`is quite different from
`that of Excel. When a map is active, Microsoft Map menus and
`toolbars
`replace Excel's menus and toolbars.
`
`Mapping: An Overview
`
`is a tool that visually presents data.
`like charting,
`Mapping,
`People use maps for a variety of purposes, but the common
`fact01· in maps is that they work with data that has a basis in
`If you classify information by state, province,
`geography.
`01·
`country, chances are good that you can represent
`the data on
`if your company sells its products
`a map. For example,
`throughout
`the United States, showing
`the annual sales for
`each state may be useful.
`
`emnosUSA0017063
`
`CiM Ex. 1053 Page 5
`
`

`
`A Mapping Example
`Figure 17c:,J shows sales data for a company, with the data categorized by state. To
`spend a fof bf time exaniining the
`und~ffsta.n.d this inforrriation, you Wouklhave
`data.
`
`in a chatt. Althot1gh ,'ulimprovement
`Fig,1re 17,2 shows the same data displayed
`doesn't really work, bec;mse it
`over the raw·d;:rta table., this type ofpresentation
`has too many data points. In a;:lditfon, the chart doesn't re11eal any inform,uticin
`abo:ut sales in a partic:u.lar region,
`
`if the sales were rc:presented
`rtiqre revealing
`be evm
`The map in Figure
`is/in t)er capitasales.,This
`population
`.. 0Le.ach.t1tat.e; that
`relative
`t1J the pvpulation
`data is availabl1;:: as a sarnple file
`the Qffice CD (MapstatS.xls}
`
`emnosUSA0017064
`
`CiM Ex. 1053 Page 6
`
`

`
`Figure 17-2: The sales data displayed in a chart.
`
`Figure 17-4 shows
`
`the contents
`
`sheet for this workbook
`
`'1fl 0011 +..... ~:111 nnri
`4l1Uon+,, 1n.nm
`Otn
`40,000
`
`1C'
`1 0)
`rs1
`5)
`
`Figure 17-3: The sales data displayed in a map.
`
`Available Maps
`a good variety of maps and enables you to
`The Microsoft Map feature supports
`formats. A single map can display multiple sets of
`create maps in several different
`data, each in a different
`format. F'or example, your map can show sales by state and
`the number of sales offices in each state. In addition, your map can display
`indicate
`other accoutrements,
`such as labels and pin markers.
`
`emnosUSA0017065
`
`CiM Ex. 1053 Page 7
`
`

`
`HE ?¥rt$;¥§tsV V
`
`CiM Ex. 1053 Page 8
`
`emnosUSA0017066
`
`CiM Ex. 1053 Page 8
`
`

`
`lf you would like to order additional maps or data from Maplnfo, you can contact
`the company directly or visit its Web site. For information on how to do so, activate
`a map and click the Help• About command.
`
`Creating a Map
`
`Creating a basic map with Microsoft Map is simple. In ahnost all cases, however,
`the basics of mapmaking.
`you'll want to customize
`the map. This section discusses
`
`Setting Up Your Data
`in a list format (for an example,
`The Microsoft Map feature works with data stored
`refer to Figure 17-1 ). The first column should contain names of map regions (such
`as states or countries). The columns
`to the right should contain data for each area.
`You can have any number of data columns, because you select the columns
`to use
`after the map is created.
`
`Creating the Map
`the data. The selection must include one column
`To create a map, start by selecting
`of area. names and at least one column of data. If the columns have descriptive
`headers,
`include
`these
`in the selection.
`
`toolbar). Click and
`Choose Insert• Map (or click the Map button on the Standard
`drag to specify the location and size of the map or just click to create a map of the
`default size. Unlike charts, maps must be embedded on a worksheet
`(there are no
`separate map sheets).
`
`the appropriate map. If two
`the area labels and generates
`Microsoft Map analyzes
`or more maps are possible
`(or if you've developed
`any custom map templates),
`you'll see the Multiple Maps Available dialog box. shown in Figure 17-5. Select the
`map that you want to use from this list.
`
`Figure 17-5: If multiple maps are available for
`your data, you can choose from this dialog
`box which map to use.
`
`emnosUSA0017067
`
`CiM Ex. 1053 Page 9
`
`

`
`feature, you need to
`To install the mappin€
`teelture. Mict'O.'JOU
`IVHcrosoftOffic'l'i Setup.1,rogramJ and spedfy the·mapping
`dis:playsthe m'.ap using the first column
`data. It also displays
`the Milcros,oft
`lat1;,r in thii! chapter. When thema:p
`is created,
`Control dialog box, discussed
`activated,Wher1.ever a map is activated, Mkrci~c)ft Map's
`a11d tOolbar re1,1ace
`Ex.c€l.'s n1enus and too1bars. When you dick outside the mai:i, Excel's user int:e1iiac.e
`re:c11)petar:S. Y•C>U CalU jre}·1ct1vi1te a rnap by double-clicking
`it.
`
`Setting the Map Format(s)
`a l!Mip fin.it appt!fll'S,
`the Mi.cr1:J$1)(t Map V\.•,u,vA.\.<A<U.'JJI
`17..6),UsethisdialofJ
`box
`us:etbe Show/Hide MapContro!
`
`tool lo
`
`the s:et<~ctecl
`
`the value-f.:.hading m,i\pfo;r111rat. Yo1~1can. <;J-1iu11
`ge
`,".,_.,,,.,,, or display two .Ql' ll1tore 1orm,;1,tl.'l on ,i cSingli;~ nra.p.
`MaJ) Control dialog box by dn1ggingthe
`ite1ns in·it, The top. Of the dialog box
`displays all avaiJable data fields (which (~or:respond
`to the i.::olunms that you
`,vhen you cre~ted the map). The hottom part contains the map format
`selected
`information, Six format icori~s on theleft deter:rtdne the tnap form1:rt (de.1crl.bed tn
`with one ox
`.l:'<)r e:x:mmrile, y,ou cr,tn t"!~J:ilacethe default"'~"'"''"''~~·,,.
`
`d:r,agJ!in,:t· CJl1elC!)tl,,
`
`emnosUSA0017068
`
`CiM Ex. 1053 Page 10
`
`

`
`the new icon over the existing one. Some
`icon with another one simply by dragging
`map formats use more than one data field. In such a case, you can drag additional
`data fields next to the icon.
`
`icon
`the format
`for a particular map format, either double-dick
`To change options
`or use the Map menu and choose
`the menu command
`that is app1·opriate
`for the
`In either case, you get a dialog box that's
`format
`that you want to change.
`appropriate
`for the map format.
`
`include descriptions
`The following sections
`supported
`by Microsoft Map.
`
`(and samples) of each map format
`
`Value shading
`With this map format, each map region is shaded based on the value of its data.
`This format
`is appropriate
`for data-quantitative
`information,
`such as sales,
`and so on. Figure 17-7 shows an example of a map formatted with value
`population,
`to show only part of the U.S.). In this example,
`shading
`(this map is zoomed
`the
`sales are broken down into four ranges, and each sales range is associated with
`different shading.
`
`1tsl :)ales
`IJ'; J~UtO:.d,UOU
`lJ
`lJ
`~ 1 , ,uto
`·1 ,8u
`]
`:so to 1 . 20 ~)
`J
`outo
`:so 2)
`
`Figure 17-7: This map uses the value-shading format.
`
`You can change
`Figure 17-8.
`
`the interval
`
`ranges
`
`in the Format Properties
`
`dialog box, shown
`
`in
`
`emnosUSA0017069
`
`CiM Ex. 1053 Page 11
`
`

`
`that ltses value shading 1 choose Map• Value Shading
`When you're vie,Ning a
`Options, and Microsoft Map.opens
`the value~shading verskm of the Fm:rnat
`Prope1·t:ies dialog box. You can specify the numher ofvalue
`ranges and the method
`of defining the nmges -- an equaJnu.mberOf areas in each 1ange or an e,4ual spread
`,of values in each rangie:. You also can sdect a q:o1or forthe shading. The rnap
`dispiays differettt variations of the s1ngle color that you select. Yotl cah. choo.se the
`stu1irnaryfonctfon toUS:e•(SUM or AVERAGE); To hidethe
`1·emove the check markfrorn
`the Visible check box.
`
`Figure 17~9 shows a rnap that usies .:;,1tego1ry
`1:'tnt1ual Sales goal.
`
`emnosUSA0017070
`
`CiM Ex. 1053 Page 12
`
`

`
`Figure 17-9: This map uses the category-shading format.
`
`in the categories, use the Format Properties dialog box. Again,
`the colors
`To change
`when you're viewing a map that uses category
`shading, you can open this dialog
`box by choosing Map• Category Shading Options. Microsoft Map displays
`the
`category-shading
`version of the Format Options dialog box.
`
`Dot density
`The dot-density map format displays data as a se1·ies of dots. Lat·ger values
`translate
`into more dots. The dots are placed randomly within a map region. Figure
`17-10 shows an example of a map that uses the dot-density
`format. This map
`100,000 people.
`depicts population
`in the U.K. and Ireland. Each dot represents
`
`the clot size, access
`the number of units for each dot or to change
`To change
`Dot Density Options
`tab of the Format Properties
`dialog box, which
`is shown
`Figure 17-11.
`
`the
`in
`
`emnosUSA0017071
`
`CiM Ex. 1053 Page 13
`
`

`
`Figtue rl•i 1: The Dot Density Options tab
`o.f t.he Forrnat Properties dialog box.
`
`Graduated symbol
`The graduated·symbol map format displays a symbol;the size of which fa
`proportional
`to tbearea·s
`daU value, FigtJre 17~12 sh(~ws an example of this forrnaL
`I used a Wingdings font chiiracter for the symbol. To change the symbol, useth-e
`GtaduatedSymhol Optionsd:ialog box/You can selectafout,
`size, and spedfk;
`
`emnosUSA0017072
`
`CiM Ex. 1053 Page 14
`
`

`
`l'J,J1th<'ct' R,-,\JIOl1
`Po1ential ror 8oftwara Sales
`
`Figure 17-12: A graduated-symbol format map.
`
`Pie chart
`The pie-chart map format requires at least two columns of data. Maps with this
`format display a pie chart within each map region. Figure 17-1:i shows an example.
`for
`the relative sales of three products
`This map shows a pie chart
`that depicts
`each state.
`
`oy citate
`
`lll '-'roduct A (1lJ)
`HTil ,'lroduc1 8 (1 U)
`E] i--'rodUCI (, CIU)
`
`Figure 17-13: A map that uses the pie-chart format
`
`emnosUSA0017073
`
`CiM Ex. 1053 Page 15
`
`

`
`tab of
`use the Pie Chart Options
`Tei change the setting for a pie-chart
`fonnat
`the format Properties dialog box, shcrw:n in Figure 17-14. This dialog box enables
`each pie sUc:e. lfyouchooselhe
`Griitduated option,
`you to select Rcolodor
`the size
`of each pie is propo1tkmal
`to the sum or average of the data. If yon don't use the
`also can SeUhe diameter oft he pi<':1,.
`Graduat?cl opti9n,
`
`Pie Chart Optio11s tab
`Figure 1144:
`of the Format Properties dialog box.
`
`The colu:mn~chart map format is «hni 1,~r tr, th,~1 Dle-cnart
`displays a tolurnu chart instead
`
`kYfIDlflt""""'·
`
`(;"J!,C~lPt
`
`Combining map formats
`As<previously mentioned, a single map can incfode multiple formats for .different
`fitacking grrn.1ps oficons and data fif.lids in the Microsoff)V[ap
`data .. Yob do this
`example, you can display sales .as value shading and the
`Control
`box.
`cust~miers as a dqt 4 den);)ity map. Eaeh map
`its own legend.
`
`emnosUSA0017074
`
`CiM Ex. 1053 Page 16
`
`

`
`/
`
`Pruduc, '-'eJle:
`by ~itate
`
`Ill ~roduct A (11]
`1U ~ruuuc1 8 ("11)
`[] ='rnduct C' (11)
`
`l
`J
`
`Figure 17-15: A map that uses the column-chart format
`
`liiil 4;cJUU,UUU
`
`b .t'.L,41..iU,UUU l11J
`.0 7,1 C0,00C
`(9)
`.o 4,8GO,OOC (1 J)
`I ,4UU,UUU O ~,UlU,UUU
`(cl)
`400,JOO .o 1,4CO,OOC (131
`
`Figure 17-16: An example of a map that uses two map formats:
`the value-shading format and the graduated-symbol format
`
`Customizing Maps
`
`customization
`After you create a map, you have numerous
`choose, which are described
`in the following sections.
`
`options
`
`from which to
`
`emnosUSA0017075
`
`CiM Ex. 1053 Page 17
`
`

`
`Zooming In and Out
`less
`Microsoft Map enables you to zoom your map in and out. Zooming in displays
`of the map and zooming out displays more of the map (or makes the entire map
`smaller). Use the Zoom Percentage of Map control on the tool bar (no menu
`commands
`exist).
`
`emnosUSA0017076
`
`CiM Ex. 1053 Page 18
`
`

`
`than 100 percent. To zoom out, select
`greater
`To zoom in, select a zoom percentage
`less than 100 percent. Before you zoom out, you might want to
`a zoom percentage
`specify the point that will be the center of the map (use the Center Map toolbar
`button).
`
`Repositioning a Map
`You'll find that, after zooming
`in or out, the map may not be optimally positioned
`within the map object rectangle. Use the Grabber
`tool to move the map image
`within the map object. Just dick and drag the map to reposition
`it.
`
`Adding Labels
`add
`Usually, a map doesn't have labels to identify areas. You can't automatically
`labels to all areas (for example, all states
`in the U.S.), but you can add individual
`labels, one at a time. You also can insert data values that correspond
`to a particular
`map region (such as sales for West Virginia).
`
`Use the Label tool to add labels or data values.
`\Vhen you click the Label tool, the
`in Figure 17-17 appears. The option button
`dialog box shown
`labeled Map feature
`names refers to labels for the various parts of the map (for example, state names
`a U.S. map). When you select
`the Values from option, you can insert data values
`from a category
`in the list box. After closing
`the dialog box, you can drag the mouse
`pointer over the map. The label or data value appears when the mouse pointer
`is
`over a map region. Just click to place the label or data value and then repeat
`this
`for each map label or data value that you want to add. Figure 17-18
`procedure
`shows a map that uses labels and data values.
`
`in
`
`Figure 17-17: The Map Labels dialog box
`enables you to add labels or data values to
`your map.
`
`the font, size,
`To move a label, dick and drag it to a new location. You can change
`it. Stretching
`the label (by dragging a border)
`or color of a label by double-dicking
`also makes the font larger or smaller.
`
`emnosUSA0017077
`
`CiM Ex. 1053 Page 19
`
`

`
`Adding Pins to a Map
`In some cases, you 111aywant to ad<l
`mote iden:tifierkons
`fo yourtnap, This
`to inserting pins in a walhnap
`fo identify various places.
`is similar
`in concept
`
`tuol displays a dfoJog box th<1tasks yo1i to enter
`Cli<::king the Custom Pin
`name for .a cuSt<)m pirU:nap
`desc:riptfye
`dtOoSe acltexistJng pi!1 :tnap).
`same pins into another rnap
`sar.ne type)
`label; you'll be able to ht1ngthese
`later. For example,
`gales
`add the
`ifyou'reidentifying
`sarne Pins to another1nap.
`
`Whf:':n you dqse the dialog hPX, the mouse pointer changes to a 1111shpin. You <:,m
`in y-01fr map. When yo(J i:.:Ikk the n:Ui.p fo plae:e a pin, yM
`place these pins anywhere
`also cmi enter descriptive
`te:xt. Du11bleccHckiug a pin en'ables you to chi:1.nge the
`symbol that is used to<something other than a pin. Figure n-19 shows
`pins added
`it.
`
`emnosUSA0017078
`
`CiM Ex. 1053 Page 20
`
`

`
`Figure 17-19: This map has pins to identify
`specific locations.
`
`Modifying the Legend
`You have quite a bit of control over the legend in a map. Note that a map displays a
`legend for each map format that it uses. To modify a legend, double-click
`it
`separate
`to see the dialog box shown in Figure 17-20.
`
`Figure 17-20: The Legend Options tab of
`the Format Properties dialog box.
`
`emnosUSA0017079
`
`CiM Ex. 1053 Page 21
`
`

`
`such as changing the n1.u:hber of data ranges
`to the legend~
`To make tither changes
`that are used ~select
`the ,ipp:ropriate m¢nu item on the Map menu. For e,,xample,
`to change the number:td range~, that are :u:sed
`vaJue-shading map format, seiect
`the Map~ Value Shading OpUons tormntmd.
`
`Adding .and Removing Features
`
`When you select Map• Featnres,
`bo,x i:stie .Fbi!Ul"e 17°21), which list$ all <1Vailabk
`f<Atrti rJ·~. pfa('.e f1 d1eck mark next fo it
`n1,~ f.::at1Ltr12is avaJll;\blevary With the
`you can add it
`
`Figure 17°22 shows
`m~1.jM highways,·<irtd.world.oceans)
`M1:;xicoJ.
`
`map with some featl1rcs added (major dtk:s,
`andsorne 1e,1tltre,s
`
`fecttures available fm· each .map.
`_:_:,:add W1>!'ld•ot;eailst,J a North Ar,r1e1nca ,,,,_,;.,., "'"
`
`to dispfoy only
`areas.
`n:uiywant yourn1ap
`In some cases/you
`ifyour cor:npa11:y does business in Missouri, UH11ofa, K,:iJ)sas;l:~nd Nebniska; you
`create a rn.ap thatShcn,vs only these four states. Create a map that includes
`these
`states and then remove all features from the map by using the Mr,p • Features
`,cofomand. The map then shows only those are,;.s that have data. figure 17-2~1 shows
`an•exampfe.
`
`emnosUSA0017080
`
`CiM Ex. 1053 Page 22
`
`

`
`Figure 17-22: This map has features added and removed.
`
`Map
`
`Canada
`
`Features
`
`Airports, Cities, Forward Sortation Areas, Highways, Lakes,
`Major Cities
`
`Mexico
`
`Cities, Highways, Major Cities
`
`U.S. in North America
`
`5-Digit Zip Code Centers, Highways, Major Cities, Great Lakes
`
`World
`
`Capitals, Countries, Graticule, Oceans
`
`emnosUSA0017081
`
`CiM Ex. 1053 Page 23
`
`

`
`Figure 11~24: .The Spedfy Geogrqphk Data.
`dialog box enables you to select the data to
`use as geographic data.
`
`emnosUSA0017082
`
`CiM Ex. 1053 Page 24
`
`

`
`lf you want to create a map that uses ZIP codes, make sure that your ZIP codes are
`formatted
`as values, not as text. Otherwise, Microsoft Map won't recognize
`them as
`ZIP codes.
`
`Because ZIP codes are continually being added, Microsoft Map may not recognize
`If Microsoft Map encounters
`all of your ZIP codes.
`an unknown ZIP code, you
`the Resolve Unknown Geographic Data dialog box, shown
`in Figure 17-25.
`receive
`This dialog box gives you the opportunity
`to change
`the ZIP code to another one.
`that item of data by clicking the Discard button.
`Or, you can simply discard
`
`Figure 17-25: Microsoft Map displays the
`Resolve Unknown Geographic Data dialog box
`when it doesn't recognize a geographic name.
`
`by their ZIP codes. This is a
`Figure 17-26 shows a map that depicts customers
`graduated-symbol
`map (the default fonnat when ZIP codes are used as data). Note
`that the symbols appear on the geographic
`centers of the ZIP codes and don't
`shade
`the entire ZIP code areas.
`
`Figure 17-26: This map shows customers by ZIP
`code centers.
`
`emnosUSA0017083
`
`CiM Ex. 1053 Page 25
`
`

`
`Adding More Data to a Map
`create a mar>, you can add additional data to it. u::1e 1,11e
`After
`command to ?tdd dat.:droma woikf:!heet range, 01· use the Insert Exten1t,1.l Data
`to add new data from a databas.e file,-Make sun.dhat
`command
`the data includes
`labels that i:natd1 th€ rnap to whkh you're adding data.
`g:ebgraphk
`
`right can sometimes
`now, getting a rnapjttst
`a map te:n1pla.t e, so that you
`u1c11) and
`rnap. Tc,
`s(i>cxeate and custnn1i2elh.e
`another
`settings
`Map• S,weMp;pTernplate, Yau can savf; ~-template
`that include.5 the following:
`
`that you've added or :re1tncivect
`The features
`Apartkular.view{zoomed
`in or
`Both
`t:he prece.ding}tems
`
`Saved ten:1plates then appear
`anDe,ars when you
`
`in the MuifitJle Ma.ps Available diafog hok, which
`
`Converting a Map to a Picture
`
`You'll find that working with 1t1aps sometimes Cari be tathersluggish'--Ca
`great deal
`wnrk goes on. behind the scenes, When ym1lh1ish withyour map, you can
`that is no longer linked to the data. Click the mapunce
`-convert ILto a static picture
`to select it (don't dQul)le--click 1t} and then choose Edit •
`Then, select Ed.it•
`ch()ose the Pidu:re (Enhanced Metafile) option. This creates an
`Paste Spedaland
`unlinked picture of the map.ThE,n, you can selecttheorigit1al
`lU,;lp object and
`delete
`iL
`
`Learning More
`
`tiine to
`is :relatively comp.\e,'C, and it de{ihitelytakes
`The Mictosoft Map feature
`master, The best
`master this feature is sir.nplyto
`scane maps and
`perform
`the
`is dlff e.rent
`mentioned,
`
`emnosUSA0017084
`
`CiM Ex. 1053 Page 26
`
`

`
`from Excel's, so you'll have to try some new techniques. Generally, you can find
`your way around maps by doing the following:
`
`• Double-clicking
`
`objects
`
`• Right-clicking objects
`
`• Exploring
`
`the menus (they change somewhat,
`
`depending on the type of map)
`
`• Using the Microsoft Map toolbar
`
`Summary
`
`is actually an OLE
`covers ExceJ's new Microsoft Map feature -which
`This chapter
`server application
`developed
`by Maplnfo Corporation.
`Some data is more
`appropriate
`for a map than for a chart, and this chapter demonstrates
`the
`difference. This chapter also describes
`the basics of creating and customizing maps
`and provides an example of each map format.
`
`emnosUSA0017085
`
`CiM Ex. 1053 Page 27
`
`

`
`CiM Ex. 1053 Page 28
`
`emnosUSA0017086
`
`CiM Ex. 1053 Page 28
`
`

`
`Creating and
`Using
`Worksheet
`Outlines
`
`If you use a word processor, you may be familiar with the
`
`have an
`concept of an outline. Most word processors
`outline mode that lets you view only the headings and
`subheadings
`in your document. You can easily expand a
`heading
`to show the detail (that
`is, the text) below it. To w1·ite
`in my word processor
`this hook, I used the outline feature
`extensively.
`
`Excel also is capable of using outlines, and understanding
`featln-e can make working with certain
`types of worksheets
`much easier for you.
`
`this
`
`Introducing Worksheet Outlines
`
`in which you
`reports
`to create summary
`You can use outlines
`don't want to show all the details. You'll find that some
`If your
`worksheets
`are more suitable
`for outlines
`than others.
`worksheet
`uses hiernrchical
`data with subtotals,
`it's probably
`for ,m outline.
`a good candidate
`
`An Example
`outlining works
`how worksheet
`The best way to understand
`to look at an example. Figure 18-1 shows a simple budget
`model without an outline. Subtotals are used to calculate
`subtotals
`by region and by quarter.
`
`is
`
`emnosUSA0017087
`
`CiM Ex. 1053 Page 29
`
`

`
`Figure 18-1: A typical budget model with subtotals.
`
`after the outline was created. Notice that
`Figure 18-2 shows the same worksheet
`Excel adds a new border
`to the left of the screen. This border contains controls
`enable you to determine which level to view. This pa1·ticular outline has three
`levels: States, Regions (each region consists of states), and Grand Total (the sum of
`In Figure 18.2, the outline
`is fully a'<:panded so that you can
`each t·egion's subtotal).
`see all the data.
`
`that
`
`Figure 18-2: The budget model after creating an outline.
`
`level. Now, the outline
`the outline displayed at the second
`Figure 18-3 depicts
`shows only the totals for the regions (the detail rows are hidden). You can partially
`
`emnosUSA0017088
`
`CiM Ex. 1053 Page 30
`
`

`
`region. Collapsing
`to show the detail for a particular
`the outline
`expand
`to level 1 shows only the headers and the Gnmcl Total row.
`
`the outline
`
`Figure 18-3: The budget model after collapsing the outline to the
`second level.
`
`In the preceding examples,
`in both directions.
`the outline
`Excel can create outlines
`was a row (vertical) outline. Figure 18-4 shows
`the same model after a column
`outline was added. Now, Excel displays another border at the top.
`(horizontal)
`
`Figure 18-4: The budget model after adding a column outline.
`
`lf you create both a row and a column outline in a worksheet, you can work with
`of the other. For example, you can show the row outline
`each outline independent
`level and the column outline at the first level. Figure 18-5 shows the
`at the second
`model with both outlines collapsed at the second
`level. The result is a nice
`summary
`table that gives regional totals by quarter.
`
`emnosUSA0017089
`
`CiM Ex. 1053 Page 31
`
`

`
`You'll find the workbook used in the preceding examples on this book's CD-ROM.
`
`Figure 18-5: The budget model with both outlines collapsed at the
`second level.
`
`More About Outlines
`The following are points
`to keep in mind about worksheet outlines:
`
`If you
`can have only one outline (row, column, or both).
`• A single worksheet
`need to create more than one outline, move the data to a new worksheet.
`• You can either create an outline manually or have Excel do it for you
`If you choose
`automatically.
`the latter option, you may need to do some
`format.
`in the proper
`preparation
`to get the worksheet
`
`• You can create an outline for either all data on a worksheet or just a selected
`data range.
`• You can remove an outline with a single command.
`
`• You can hide the outline symbols
`• You can have up to eight nested
`
`(to free screen space) but retain the outline.
`levels in an outline.
`
`Worksheet outlines can be quite useful. But if your main objective
`is to summarize a
`large amount of data, you might be better off using a pivot table. A pivot table is
`much more flexible and doesn't
`require
`that you crnate the subtotal
`formulas;
`it
`does the summarizing
`for you automatically.
`
`Pivot tables are discussed in Chapter 25.
`
`emnosUSA0017090
`
`CiM Ex. 1053 Page 32
`
`

`
`Creating an Outline
`
`In this section, you learn the two ways to create an outline: automatically
`manually. But, before getting
`into the details of those
`two methods,
`the all(cid:173)
`important
`first step is examined: getting your data ready for outlining.
`
`and
`
`Preparing the Data
`Before you create an outline, you need to ensure
`
`the following:
`
`• The data is appropriate
`
`for an outline
`
`• The formulas are set up properly
`
`Determining appropriate data
`the data should be
`What type of data is appropriate
`for an outline? Generally,
`arranged
`in a hierarchy,
`such as a budget
`that consists of an arrangement
`similar to
`the following:
`
`Company
`
`Division
`
`Department
`
`Budget Category
`
`Budget Item
`
`item (for e,'{ample, airfare and hotel expenses)
`In this case, each budget
`is part of a
`(for example,
`travel expenses). Each department
`has its own
`budget category
`budget, and the departments
`are rolled up into divisions. The divisions make up the
`company. This type of arrangement
`is well-suited
`for a row outline-although
`most
`of your outlines probably won't have this many levels.
`
`at any level of detail that you want.
`Once created, you can view the information
`When you need to create reports
`for different
`levels of management,
`try using an
`outline. Upper management may want to see only the Division totals. Division
`managern may want to see totals by department,
`and each department manag€f
`needs
`to see the full details for his or her department.
`
`time-based
`at the beginning of the chaptei-, you can include
`As demonstrated
`in a
`information
`that is rolled up into larger units (such as months and quarters)
`column outline. Co]umn outlines work just like row outJines, however, and the
`levels need not be time-based.
`
`emnosUSA0017091
`
`CiM Ex. 1053 Page 33
`
`

`
`Setting up the formulas
`formulas
`Before you create an outline, you need to make sure that all the summary
`,u-e in
`are entered correctly and consistently. Consistently means that the formulas
`the same relative
`location. Generally, formulas
`that compute summary
`formulas
`(such as subtotals)
`are entered below the data to which they refer. In some cases,
`however,
`the summary
`formulas are entered above the referenced
`cells. Excel can
`handle either method, but you must be consistent
`throughout
`the range that you
`outline. If the summary
`formul.:-ts aren't consistent,
`automatic outlining wont
`pr,oduce the results
`that you want.
`
`If your summary formulas aren't consistent (that is, some are above and some are
`below the data), you still can create an outline, but you must do it manually.
`
`Creating an Outline Automatically
`Excel can create an outline for you automatically
`in a few seconds, whereas
`take you ten minutes or more to do the same thing manually.
`
`it might
`
`To have E..xcel create an outline, move the cell pointer anywhere within the range of
`data that you're outlining. Then, choose Data• Group and Outline• Auto Outline.
`E.'!:cel analyzes
`the formulas
`in the range and creates
`the outline. Depending on the
`formulas
`that you have, Excel creates a row outline, a column outline, or both.
`
`already has an outline, Excel asks whether you want to modify the
`If the worksheet
`existing outline. Click Yes to force Excel to remove
`the old outline and create a new
`one.
`
`Excel automatically creates an outline when you use the Data• Subtotals com(cid:173)
`if you set up your data
`mand, which inserts subtotal formulas automatically
`as a list
`
`The Data• Subtotals command
`"Creating Subtotals").
`
`is discussed
`
`in Chapter 23 (see the section
`
`Creating an Outline Manually
`It's much faster and
`Usually, letting Excel create
`the outline
`is the best approach.
`If the outline that Excel creates
`isn't what you have in mind,
`less error-prone.
`however, you can create an outline manually.
`
`rows all must be above the data or
`the summary
`When Excel creates a niw outline,
`below the data (they can't be mixed). Similarly, for a column outline, the summary
`columns all must be to the right of the data or to the left of the data. If your
`worksheet doesn't meet these requirements,
`you have two choices:
`
`• Rearrange
`
`the worksheet
`
`so that it does meet the requirements
`
`• Create the outline manually
`
`emnosUSA0017092
`
`CiM Ex. 1053 Page 34
`
`

`
`if the range doesn't contain any
`You also need to create an outline manually
`it
`formulas. You may have imported a file and want to use an outline
`to display
`better. Because Excel uses the formulas
`to determine how to create
`the outline,
`not able to make an outline without
`formulas.
`
`it is
`
`Creating an outline manually consists of creating groups of rows (for row outlines)
`(for column outlines). To create a group of rows, click the row
`or groups of columns
`numbers
`for all the rows that you want to include
`in the group-
`but do not select
`the row that has the summary
`formulas. Then, choo