`
`
`
`Exhibit 2014, Excel III
`Exhibit 2014, Excel Ill
`
`26
`
`Paltl 9 Getungstarred
`
`Select All Button
`
`Clicking the intersection of the row and column headers selects all cells on the
`active worksheet of the active window.
`
`Active Cell Indicator
`
`This dark outline indicates the currently active cell (one of the 16,777,216 cells on
`each worksheet).
`
`Row Headings
`Numbers ranging from 1 to 65,536—one for each row in the worksheet. You can
`click a row heading to select an entire row of cells.
`
`Column Headings
`Letters ranging from A to lV—one for each of the 256 columns in the worksheet.
`Alter column Z comes column AA. which is followed by AB, AC. and so on. After
`column AZ comes BA, BB, and so on until you get to the last column, labeled N.
`You can click a column heading to select an entire column of cells.
`
`Tab Scroll Buttons
`
`These buttons let you scroll the sheet tabs to display tabs that aren't visible.
`
`Sheet Tabs
`
`Each of these notebook-like tabs represents a different sheet in the workbook. A
`workbook can have any number of sheets. and each sheet has its name displayed in
`a sheet tab. By default, each new workbook that you create contains three sheets.
`
`Tab Split Bar
`This bar enables you to increase or decrease the area devoted to displaying sheet
`tabs. When you show more sheet tabs, the horizontal scrollbar's size is reduced.
`
`Horizontal Scrollbar
`
`Allows you to scroll the sheet horizontally.
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 1 of 17
`Exhibit 2014/ Page 1 of 17
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`Entering and
`Editing
`Worksheet Data
`
`People usespreadsheets primarilyto storedataand per-
`
`form calculations. This chapter discusses the various
`types of data that you can enter into Excel.
`
`Types of Worksheet Data
`As you know, an Excel workbook can hold any number of
`worksheets, and each worksheet is made up of cells. A cell
`can hold any of three types of data:
`
`6 Values
`
`0 Text
`
`0 Formulas
`
`A worksheet also can hold charts. maps. drawings. pictures.
`buttons. and other objects. These objects actually reside on
`the worksheet's draw layer. which is an invisible layer on top
`of each worksheet.
`
`, aw -
`. ”an”?
`./
`
`The draw layer is discussed in Chapter 14. This chapter is
`concerned only with data that you enter into worksheet cells.
`
`Values
`
`Values, also known as numbers. represent a quantity of some
`type: sales. number of employees. atomic weights. test scores.
`and so on. Values that you enter into cells can be used in
`formulas or can be used to provide the data that is used to
`
`I
`
`CHArPTER
`
`Q
`
`Q
`
`9
`
`Q
`
`In This Chapter
`
`Types of Worksheet
`Data
`
`Entering Values
`
`Entering Text
`
`Dates and Times
`
`Changing or Erasing
`Values and Text
`
`Formatting Values
`
`Formatting Numbers
`Using Shortcut Keys
`
`Basic Cell Formatting
`
`Dala Entry Tips
`
`9
`
`0
`
`O
`
`O
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 2 of 17
`Exhibit 2014/ Page 2 of 17
`
`
`
`
`
`
`
`
`
`94 Paloma-ups
`
`create a chart. Values also can be dates (such as 6/9/2019) or times (such as 3:24
`am.) and you'll see that you can manlpulate these types oi values quite efficiently.
`
`Figure (Ll shows a worksheet with some values entered in it.
`
` —rr‘.
`
`figumG-l: Valuesenteredinaworksheet.
`
`Text
`Most worksheets also Include non-numeric text in some oi their cells. You can
`
`Insert text to serve as labels [or values. headings tor columns, or Instructions about
`the worksheet. Text that begins with a number is still considered text. For example.
`it you enter an address such as 145 Mill St. Into a cell, Excel considers this to he
`text rather than a value.
`
`figure 6-2 shows a worksheet with text In some at the cells. In this case. the text is
`used to clarity what the values mean-
`
`Inn-st Illu-
`
`M‘mNy "SM
`
`fatal Pain-ms
`
`figIIIG-z: lhisworbheetmnsistsoftextmdvalues.
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 3 of 17
`Exhibit 2014/ Page 3 of 17
`
`
`
`
`
`
`
`
`
`aqua o mum-3mm 95
`
`Formulas
`
`Formulas are what make a spreadsheet a spreadsheet—otherwise. you'd just have
`a strange word processor that Is good at working with tables. Excel enables you to
`enter powerlul lormulas that use the values (or even text) In cells to calculate a
`result. When you enter a formula Into a cell. the tormula‘s result appears In the cell.
`It you change any 01 the values used by a lormula. the formula recalculates and
`shows the new result. figure 6-3 shows a worksheet wlth values. text. and lonnulas.
`
`Chapter 9 d.“ formulas in detai.
`
`Lam Tum from)
`lllmt file
`
`mum
`27am
`3)
`0 ‘53
`
`51531565
`
`MarmyPagmml
`Yaquamms
`
`| 2063
`
`“FIG-3: CelkBsand B9mntainformulasthausetheothervalues.
`
`Excel’s Numerical Limitations
`
`NumdlmaemriusabulflletwesdnhesflflEId-Ihmdhhdhamds,
`howlayemmmrbasbe‘lkldllowaMarelaluem-M
`
`Encel’sm-nbersalepredsemm IsrfillsrorenrllphiyouerlIralauvdIAsudlas
`123,123,123,123,123,123 (18%),Excdachflyshnsiuthonly156flsdprecisim:
`123,123,123,123IBMIIanmmhmMmmtmdy-luaw
`
`HaemsunedEmel'sothermmeridimis:
`
`WWW-9.99807
`
`SmiestposiinmmberzlE-m
`
`WWW—15307
`
`anhsmmmdmsdgficnmrormmemm
`munber's'sst'lnslotothemthpowa!
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 4 of 17
`Exhibit 2014/ Page 4 of 17
`
`
`
`
`
`118 ”Iowa-ups
`
`Forcing a New line in a Cell
`It you have lengthy text In a cell. you can force Excel to display It In multiple lines
`within the cell. Use AltoEnter to start a new line In a cell. Figure 6-14 shows an
`example oi text In a cell that is displayed In multiple lines. When you add a line
`break. Excel automatically changes the cell's lorrnat to Wrap Text.
`
`, 0- g
`
`Loam moreaboutihewrap‘l’enformaiingfeahne in Olapter II.
`
`Income
`Expenses
`N91
`
`For Oilnhal Pu Plan 2
`Bugs! Nodal Binge!
`83,2]
`851,5“
`578232
`722.?!)
`1111201
`l$,7‘51
`
`
`
`inns-I4: Alt+Enterenablesyoutoforoealinebrea
`inacel
`
`Entering Fractions
`It you want Excel to enter a traction into a cell. leave a space between the whole
`number and the traction. For example. to enter the decimal equivalent of 67/3.
`enter 6 7/8 and then press Enter. When you select the cell, 6.875 appears In the
`iormula bar. and the cell entry appears as a iractlon. It you have a traction only
`(lor example. lIa). you must enter a zero iirst. like this: 0 1/8. When you select the
`cell and look at the lormula bar. you see 0.l25. In the cell. you see 1/3.
`
`Using a Data Entry Form
`ll you're entering data that Is arranged In rows. you may llnd it easier to use Excel’s
`built-In data form [or data entry. Figure 6-15 shows an example oi this.
`
`
`
`
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 5 of 17
`Exhibit 2014/ Page 5 of 17
`
`
`
`
`
`126 Human-pi:
`
`Hiding and Unhiding a Worksheet
`In some cases. you may want to hide a worksheet. Hiding a worksheet is useful it
`youdon'twantotherstoseeltorlustwanttogetitoutolthewayWhenasheetis
`hidden. its sheet tab is hidden also.
`
`To hide a worksheet. choose Format oSheet 0 Hide. The active worksheet (or
`selected worksheets) will be hidden irom vim Every workbook must have at least
`one visible sheet. so Excel won‘t allow you to hide all the sheets in a workbook.
`
`To unhide a hidden worksheet. choose Format-oSheet-o Unhide. Excel opens a
`dialog box that lists all hidden sheets. Choose the sheet that you want to redlsplay
`and click OK. You can't select multiple sheets from this dialog box. so you need to
`repeat the command for each sheet that you want to redisplay.
`
`Zooming Worksheets
`Excel enables you to scale the size at your worksheets. Normally. everything you
`see onscreen is displayed at 100 percent. You can change the 'zoom percentage'
`[tom 10 percent (very tiny) to 400 percent (huge). Using a small zoom percentage
`can help you to get a bird‘s-eye view oi your worksheet. to see how it's laid out.
`Zooming in is useful it your eyesight isn‘t quite what it used to be and you have
`trouble deciphering those 8-point sales iigures. Figure 7-4 shows a window zoomed
`to 10 percent and a window zoomed to 400 percent.
`
`
`
`Fig-.14: Awildowzoomedto lOperoentandaw'ndowzoomedtoJOOpercent.
`
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 6 of 17
`Exhibit 2014/ Page 6 of 17
`
`
`
`
`
`128 known-fits
`
`0-
`
`Ifyourworlsheetussnamedrangs(rdermaiaptera),you‘lltildihatzoomim
`yourworksheettoss pacerrtorlessdisplaysthenaneofthermgeoverlaid on
`dreaelklhisismefdfagedirganovaviewofhowawodsheetislaidout
`
`If you're using a Microsoft lntelliMouse (or a compatible wheel mouse). you can
`change the zoom factor by pressing Cirl while you spin the mouse wheel. Each spin
`changes thezoom factorby l5percent(butyoucan‘t zoomoutmorethan 100
`percent). if you find that you do a lot of zooming in. you can change the default
`behavior for the mouse wheel from scrolling to zooming. To change the default,
`select ToolsOOptions. click the General tab, and then select the Zoom on roll with
`lntelliMouse check box. After you make this change, you can zoom by spinning the
`wheel and you won't have to press Ctrl.
`
`Views, Split Sheets, and Frozen Panes
`Asyouaddmoreinformationtoaworksheet.youmayfindthatitgetsmore
`difficult to navigate and locate what you want. Excel includes a few options that
`enable you to view your sheet. and sometimes multiple sheets. more efficiently.
`This section discusses a few additional worksheet options at your disposal
`
`Multiple Views
`Sometimes. you may want to view two different parts of a worksheet
`simultaneously. Or. you may want to examine more than one sheet in the same
`workbook simultaneously. You can accomplish either of these actions by opening a
`new view to the workbook. using one or more additional windows.
`
`To create a new view of the active workbook, choose Windowo New Window. Excel
`displays a new window with the active workbook, similar to figure 7-7. Notice the
`text In the windows‘ title bars: Budgetxkzl and Budget.xls2.
`
`To help you keep track of the windows. Excel appends a colon and a number to
`each window.
`
`A single workbook can have as many views (that is. separate windows) as you want.
`Each window is independent of the others. in other words, scrolling to a new
`location in one window doesn‘t cause scrolling in the other window(s). This also
`enables you to display a different worksheet in a separate window. Figure 7-8 shows
`three views in the same workbook. Each view displays a different worksheet.
`
`As Chapter 8 explains. displaying multiple windows for a workbook also makes it
`easier to copy information from one worksheet to another. You can use Excel's
`drag-and-drop procedures to do this.
`
`
`
`
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 7 of 17
`Exhibit 2014/ Page 7 of 17
`
`
`
`
`
`
`
`any-1 omen-unwou-
`
`129
`
`
`‘h .l'l~.1'l m-
`y
`1
`
`
`
`[a figifi.ifiwfllfil —#Ifivf
`
`
`Jnuuy Hillary Ind Apr!
`lay
`, Won!
`IU.
`65
`£1
`23?
`329
`
`
`199
`715
`
`FF!“
`1%.
`|—_'-— I—V . '—l—l’:—I—‘ v}——¥ _——¥ 2—
`
`'
`
`fifimfimfiflfifiiu
`
`
`
`fipn'l-O: Displayitgthteemksheetsinthesanewkbook
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 8 of 17
`Exhibit 2014/ Page 8 of 17
`
`
`
`
`
`
`
`13o aroma-qr:
`
`Splitting Panes
`It you prefer not to clutter your screen with additional windows. Excel provides
`another option lor viewing multiple parts oi the same worksheet. The Window»
`Split command splits the active worksheet into two or four separate panes. The
`split occurs at the location oi the cell pointer. You can use the mouse to drag the
`individual pants to resize them.
`
`Figure 7-9 shows a worksheet split Into tour panes. Notice that row numbers and
`column letters aren't continuous. In other words. splitting panes enables you to
`display in a single window widely separated areas oi a worksheet. The two top-
`to-bottom stacked panes always have the same column headings. and the two
`side-by-side panes always have the same row headings. To remove the split pm.
`choose Windowo Remove Split.
`
`HFF
`mm...
`-—.—--_"--I -Z----.'.-
`January
`February lard- ounbur
`law-Ib- Danni-r A
`55
`54
`l 10
`‘EZ
`19.3
`QED
`Si
`118
`178
`4!
`355
`575
`620'
`333
`61
`'59]
`R);
`K39
`FEE
`3%
`‘3)
`A20
`5:?
`843
`£1
`
`‘
`
`.
`
`BE §§§§§§§§Qa§§§a§§§5§§
`
`(”momma 61
`E Ola-omiianfil
`
`E
`
`fissfifi
`
`Observation 6!
`.- Ola-Milan 05
`obs-Milan as
`(”Donation 67
`»' Observed-I fl
`Observation 69
`Q
`a Ohomiion N
`'71 HIT maul—ILLI
`
`
`figue'l—O: lhisworksheetissplitintofourpmes
`
`1|
`
`E
`
`.)
`
`Anodierwaymsplitand msplitpans'stodrageiherthevertitalorhoriaontal
`splitbanflienyounmefliemorsepoiiteroverasplitbacdlenmsepoim
`chmgesto a pairofparallel ineswiih arrows pointing outward fromeadi line.
`Funre7—iOshowswhereflresesplitbarsareloatedJoremovespfitpanesby
`usingihemouse,drqihepaneseparatoralihewaytoiheedgeofihewiidou
`oriustdouble-didrit.
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 9 of 17
`Exhibit 2014/ Page 9 of 17
`
`
`
`
`
`
`
`
`
`cup-moo mumm- 217
`
`IF
`
`'l‘helFiImctionisoneoithemost importantoiall iunctions.1‘hlsiunctioncangive
`your formulas decision-making capability.
`
`1'helFilmctiontakesthreearqunents.1'l|eiirstargumentisalogicaltestthatmust
`retmneitllermUEorFAISERIesecondargiunentistheruultthatyouwantthe
`iormulatodisplayiithelirstargumenthUE'lhethtrdargumentistheresult
`that youwant the iormulato displayll the firstargument is FALSE.
`
`lntheexamplethatiollows,theionnulareturnsl’ositivelithevaluemcellAl is
`greater than zero, and returns Negative otherwise:
`
`-lF(Al)0.'Positive' .'liegative')
`
`Notice that the iirst argument (Abil) evaluates to logical TRUEor FAlSl-L This
`iormulahasaprobleminthatitretumsthetextNegativeiitbecellisblankor
`contatns0.1'hesolutionlstouseanestedll-‘itmctlontoperiormanotherlogical
`test. 'l'herevised iormulaisasiollows:
`
`-IF(A1>0.'Pos1’tive' .IFiA1(0.'liegative' .‘Zero'))
`
`The lormula looks complicated. but when you break it down. you see that it's rather
`simple. Here's how the logic works. It Al is greater than 0. the ionnula displays
`Positive.andnothingelseisevaiuated.liAl isnotgreaterthanzero.howeve1:the
`second argument Is evaluated. The second argument is as iollows:
`
`IF(A1(0. 'llegative' . 'Zero')
`
`This is simply another [F statement that periorms the test on Al again. it it's less
`than 0, the iormula returns Negative. Otherwise, It returns Zero. You can nest IF
`statements as deeply as you need to—although it can get very coniusing alter
`three oriour levels.
`
`Using nested ll-‘ lunctlons is quite common. so understanding how this concept
`works is in your best interest. Mastering [P will definitely help you to create more
`powerlul formulas.
`
`Figure [0-7 shows an example oi using the IF iunction to calculate sales commis
`sions. In this example. the usual commission rate is 5.5 percent oi sales. lithe total
`salesoiasalesrepemceeds thesales goal, the commission ratelsii.25 percent. The
`ionnulaincellC6,shownnext,usesthelFiImctiontomakeadecisionregardmg
`which conunisslon rate to use based on the sales amount:
`
`- I H86 >-Sa‘l esGoal . BG'Bonus Rate .86 ‘Coni ssi onllate)
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 10 of 17
`Exhibit 2014/ Page 10 of 17
`
`
`
`awn own—urn; 245
`
`Shrink to Fit
`Excel includes a Shrink to [it option. which reduces the size oi the text so that It fits
`into the cell without spllllng over to the next cell.
`
`It you apply wrap text lormatting to a cell. you can't use the shrink-tom formatting
`
`Merging Cells
`When you merge cells. you don‘t combine the contents of cells. Rather. you
`combine a group at cells that occupy the same space into a single cell. Figure “-9
`shows two sets oi merged cells. Range C3263 has been merged into a single cell that
`holds the table’s title. Range 85:89 has also been merged to hold a title lor the
`table's rows.
`
`J I'll II l mu '3 .ll|ii.'_' 3",
`Inl-lml----_
`
`“#me
`Jan
`Fe.
`452
`935
`“.43.!
`am
`£92
`503
`1,109
`um
`1113::
`1,6!
`1 ,31'1
`12$?
`3,833
`Lute.
`‘
`‘
`
`01
`.
`2,833
`
`E 5 Q Q a f
`
`iglro II-o: Thetiilesforihisiableappearinmergedoells.
`
`You can merge any number oi cells. occupying any number oi rows and columns.
`However. the range that you intend to merge should be empty. except tor the upper-
`leit cell Ii any oi the other cells that you intend to merge are not empty, Excel
`displays a warning.
`
`To merge cells. select the cells that you want to merge and then click the Merge and
`Center tool on the Formatting toolbar. Dre only way to 'unmerge" cells Is to use the
`Fomrat Cells dialog box. Select the merged cell(s). open the Format Cells dialog
`box. and. on the Alignment tab. remove the check from the Merge cells box.
`
`Changing a Cell's Orientation
`You can display text horizontally. vertically. or specily an angle. To change the
`orientation. select the cell or range. open the Format Cells dialog box. and select
`the Alignment tab. Use the gauge to specify an angle between -90 and .90 degrees.
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 11 of 17
`Exhibit 2014/ Page 11 of 17
`
`
`
`
`
`
`
`
`
`
`
`
`
`awn own-u. 247
`
`Another Type of Justification
`
`mmmmmiflfimmhflioflohflymm’sm
`Mhasmllilatodouihthewmdbumdilflisdmfln
`Efloflohflymfikmfilhmmhcflsnflflhfisham
`mkmfimmiwutatntflelhflhasmbmfiudtut
`
`Yweaflymifliyflismltmflmi'sdsphydhmfimmemmm
`“Mamdmmmdimmmeflitoflohsflym
`
`hMcruni Elul. a wflthnflt
`
`
`Blisfllam Much you um and
`
`
`
`51m you din Because nth
`-
`book can curtain many
`6M” Jill C8" ammo “It!”
`find: at mum minimum" In I
`
`mglo 1h Um muslin]: to
`I01 and and 20 on You rm
`
`
`
`In Mwafl Euul. a walk book is the flu n man you walk and
`
`00 30: 6'03“ 0 the".
`810m 10!." 31015 BOCBUSé each ‘1)ka (8" COMM! many BME‘S.
`
`
`Implan- Ihe (hm on tin
`you tnorganin- venous Imus ammo mbmucn n I single file.
`-
`than will! an related
`
`
`
`Una worksheets ton“ and analyze data Vnucan «not and «It
`
`
`
`v dili I)" GMNIWVRBMI‘B SIMUIWOUBH and new CHCUWMB
`
`
`based on data from mung: mlksfell! \Mm you create 3 than,
`
`you canplato ma than on the without «1!: na "In“ an
`
`
`..
`‘l
`
`mmmmmhammnummmmmhm
`mkmMifiBihaspecfidew-Iusnflednmdhamcoflmi
`fewmshtnmfioflmiunmms).
`
`Sdedfllemlsdiaywmtoilfliy(dilmmhm)mdflmmmesdedionm
`unfitsolhafllesdedhn'saswihasmumhmdmhhbedm
`Eioflohfliuandflmlmdisflnfistlnm
`
`Mmmasmwhmahsiflnwflflwusdedhfihpamflm
`hdddhmEnflmmwdmmmmamnemm
`mummdflmhtm
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 12 of 17
`Exhibit 2014/ Page 12 of 17
`
`
`
`
`
`
`
`
`
`Working with
`Lists
`
`Research conducted by MIcrosoit Indicates that Excel Is
`irequently used to manage lists. or worksheet databases.
`This chapter covers list management and demonstrates useiul
`techniques that Involve lists.
`
`I
`What Is a list?
`A list is essentially an organized collection of Iniormatlon.
`More speclilcally. a list consists oi a row oi headers (descrip-
`tive text). iollowed by additional rows oi data. which can be
`values or text. You may recognize this as a database table—
`which is exactly what It Is. Beginning with Excel 5. Microsoit
`uses the term list to reler to a database stored In a worksheet
`
`{9.1)
`_l J
`
`In ‘lllis Cw
`
`Isa list?
`MW? $5.9m Do
`0
`
`Designing a list
`
`wow-'00
`
`”bring a list
`
`Using Dddioso
`flirtatious with lists
`
`and the term database to reler to a table oi Iniormation stored
`in an external ille. To avoid contusion. l adhere to Microsoit's
`
`Sorting a list
`
`terminology.
`.
`. b ‘\
`1m lcoverextemaldatabasefilesundrapteru.
`
`Croding Srbuds
`
`.
`
`.
`
`.
`
`.
`
`Figure 23-] shows an example oi a lIst In a worksheet. This
`particular list has Its headers in row i and has lo rows oi data.
`The list occupies iour columns. Notice that the data consists
`oi several dIiierent types: text. values. and dates. Column C
`contains a ionnula that calculates the monthly salary irom the
`value In column B.
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 13 of 17
`Exhibit 2014/ Page 13 of 17
`
`
`
`
`
`512 ”NOMIN-
`
`J! vrlrunr'. r-
`
`5’?G 1
`
`3533 ”(W'flilfl
`LIB Anton:
`5.550 llm'lcni-
`1,651 \innestu
`3,?‘511 Autumn
`4333 mw'rau
`1.052 Dlm‘rcd‘
`2.042 Ilnncsuta
`4.7m Ant-in:
`5.35] “muesli;
`
`2'1 '33
`ram
`“11233
`I I’d9'5
`333.8]
`SQBL
`1 1 I21 93
`we]
`6‘18?
`
`People often refer to the columns In a list as fields and to the rows as records. Using
`this terminology. the list shown in the figure has five fields (Name. Annual Salary.
`Monthly Salary. Location. and Date Hired) and ten records.
`
`The size of the lists that you develop In Excel is limited by the size of a single work-
`sheet. In other worrk. a list can have no more than 256 fields and can consist of no
`more than 65.535 records (one row contains the field names). A list of this size
`would require a great deal of memory and even then may not be possible. At the
`other extreme. a list can consist of a single cell —not very useful. but it‘s still
`consideredallst.
`
`lnversronsofEmcelpnortoEmcelnalstuaslm-Edtolsfiasrecords.
`
`'-
`
`‘\
`
`What Can You Do with a list?
`
`Excel provides several tools to help you manage and manipulate lists- Consequently.
`people use lists for a wide variety of purposes. For some users. a list Is simply a
`method to keep track of information (for example. customer lists): others use lists to
`store data that ultimately will appear in a report. Common list operations include:
`
`6 Entering data Into the list
`
`0 filtering the list to display only the rows that meet certain criteria
`
`0 Sorting the list
`
`9 Inserting formulas to calculate subtotals
`
`6 Creating formulas to calculate results on the list filtered by certain criteria
`
`6 Creating a summary table of the data in the list (this is done using a pivot
`table: see Chapter 25).
`
`
`
`
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 14 of 17
`Exhibit 2014/ Page 14 of 17
`
`
`
`
`
`h.~
`
`HI-r
`
`PW
`
`sanity
`[no
`ITFF31==I--’-----
`mm:
`-
`harks
`Eusin)
`trams:
`my
`Hart:
`Eostm
`Jamirr.
`New
`Jami";
`6mm)
`in rh:
`New
`“Min-F.
`New
`Jen-ins
`POIW
`Ji‘mliré:
`N?”
`dentin:
`New
`Jlnlin':
`Edam
`Pam:-
`rm
`”alarm
`Mm
`
`E‘I‘KW
`NEW
`Emma
`Emma
`
`Figure 23-23: Excel added the subtotal formulas automatically-and
`even creaed an outine.
`
`Summary
`In this chapter. I discuss lists. A list Is simply a database table that is stored on a
`worksheet. The first row oi the list (the header row) contains field names. and
`subsequent rows contain data (records). I otter some pointers on data entry and
`dlscuss two ways to litter a list to show only rows that meet certain criterta.
`Autol-‘tltertng is adequate tor many tasks. but it your filtering needs are more com-
`plex. you need to use advanced filtering. I end the chapter with a discussion of
`sorting and Excel's automatic subtotal teature.
`
`O
`
`O
`
`O
`
`
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 15 of 17
`Exhibit 2014/ Page 15 of 17
`
`
`
`
`
`552 Pl!" 0mu
`
`0 Name:1'henarneoftheexternaldatarange-Youcanchangethisnameoruse
`the default name that Excel creates. Excel substitutes. In the range name. the
`underscore character for any spaces that you see In the Name box of the
`External Data Range Properties box.
`
`6 Query definition: If you check Save query definition. Excel stores the query
`definition with the external data range, enabling you to refresh the data or
`edit the query. If necessary. If the database requires a password, you can also
`storethepasswordsothatyoudon'tneedtoenteritwhenyou refreshthe
`tiller!-
`
`0 Refresh control: Determines how and when Excel refreshes the data.
`
`9 Data Inn-tiling and layont: Determines the appearance of the external data
`range.
`
`The External Data Range Properties dialog box has quite a few options. For specific
`details, click the Help Icon In the title bar and then click an option In the dialog box.
`
`You can maniprlate data returned from a query just like any other worksheet range.
`For example, you can sort the data. format It. or create formulas that use the data.
`
`\
`
`InpnmvasimsofExcelifyouimdiordreshflreqmyoumedtokeepflre
`extenddlamigeimlhmhyoucamhsennewmormlumnsindre
`MMWMMrdrdingmequery-msflQMIangetobe
`rewfitinn.Sim1ady,you|osemyformafingdiatyouappiedtothemnaldm
`ramewhenyourefredlthequery.
`
`InExoelzom.rdrdlingamdaesnmovavnieflieememaldatarmge.You
`areheetofornntdreenemaldmnngemimertmsmdcolumhuakom
`iidudefanulasindroserowsmdodumsflmrefermodrerputsofflremter-
`nddatararlgevourworkwilnarbedesiroyedwhenyourefrshthequery.
`
`Refreshing a Query
`After performing a query. you can save the file and then retrieve It later. The file
`contains the data that you originally retrieved from the external database. Tire
`external database may have changed. however. In the Interim.
`
`If you checked the Save query definition option in the Extemal Data Range Properties
`dialogboxflrarExcelsavesthemrerydeflnMonmthmewukbookSImplymovethe
`cdlpoIMeranywherewimmtheenenuldatatafleinmeworksheetmdflrenuse
`one of the follmrtng methork to refresh the query.
`
`9 Right-click and select Refresh Data from the shortcut menu
`0 Select Data-o Refresh Data
`
`0 Click the Refresh Data tool on the External Data toolbar
`
`
`
`
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 16 of 17
`Exhibit 2014/ Page 16 of 17
`
`
`
`
`
`
`
`owns 0 unmade-nuns 553
`
`Excel launches Query and uses your original query to bring In the current data from
`the external database.
`
`lip
`
`If you fnd that relmhing the query causes undsirable mulls, use Excel's Undo
`) feature to ”unrefirsh' the daa.
`
`Making Multiple Queries
`A single workbook can hold as many external data ranges as you need. Excel gives
`each query a unique name. and you can work with each query independently. Excel
`automatically keeps track of the query that produces each external data range.
`
`Copying or Moving a Query
`After perlormlng a query. you may want to copy in move the external data range.
`which you can do by using the normal copy. cut. and paste techniques. However.
`make sure that you copy or cut the entire external data range—otherwise. the
`underlying query is not copied. and the copied data cannot be refreshed.
`
`Deleting a Query
`It you decide that you no longer need the data that Is returned by a query. you can
`delete it by selecting the entire external data range and choosing Edit 0 Delete.
`6 flyousimplypr‘DdetefiieaxnentsoldieoelkareeMbmflieuMedyilg
`\
`query remainsExceldisplaysadidog box aslringwhetheryouwaittodeletethe
`quayflywdmoseNOJourznrefreshfliequemanddiedeletedcelsappear
`again, induding anyformattingthdyou wpliedinthem.
`
`meurdreshQueryrehmsonlydatadmisreoiaedfmnflieextemal
`database. Ifyoudeletemsoroolumnsthatyouinserted inioilieextemaldata
`ramQuerydoesnotred'splayihoserowsand columnswhenyourelresh.
`
`Changing Your Query
`It you bring the query results into your worksheet and discover that you don‘t have
`what you want. you can modify the query. Move the cell pointer anywhere within
`the external data table In the worksheet and then use one oi the lollowing methods
`to relresh the query:
`
`0 Rightcllck and select Edit Query [mm the shortcut menu
`
`0 Select Data-oGet External Data-o Edit Query
`
`9 Cllck the Edit Query tool on the External Data toolbar
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 17 of 17
`Exhibit 2014/ Page 17 of 17
`
`