`
`
`
`Exhibit 2014, Excel III
`Exhibit 2014, Excel Ill
`
`26
`
`__s~Part |
`
`Getting Started
`
`Select All Button
`Clicking the intersection of the row and column headersselectsall cells on the
`active worksheetof 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
`Numbersranging from | to 65,536 — onefor each row in the worksheet. You can
`click a row headingto select an entire row ofcells.
`
`Column Headings
`Letters ranging from A to |V—onefor each of the 256 columnsin the worksheet.
`After column Z comes column AA,whichis followed by AB, AC, and so on.After
`column AZ comesBA, BB, and so on until you get to the last column,labeled IV.
`You can click a column headingto select an entire columnofcells.
`
`Tab Scroll Buttons
`
`These buttonslet youscroll the sheet tabs to display tabsthat aren't visible.
`
`Sheet Tabs
`
`Each of these notebook-like tabs represents a different sheet in the workbook. A
`workbook can have any numberof sheets, and each sheethas its namedisplayed in
`a sheet tab. By default, each new workbookthat 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 moresheettabs,the horizontal scrollbar’s size is reduced.
`
`Horizontal Scrollbar
`Allowsyoutoscroll 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
`
`p....usespreadsheetsprimarilytostoredataandper-
`
`form calculations. This chapter discusses the various
`types of data that you can enterinto Excel.
`
`Types of Worksheet Data
`
`As you know,an Excel workbook can hold any numberof
`worksheets, and each worksheetis madeupofcells. A cell
`can hold any of three types of data:
`
`+ Values
`
`+ Text
`
`Formulas
`
`A worksheetalso can hold charts, maps, drawings, pictures,
`buttons, and other objects. These objects actually reside on
`the worksheet’s drawlayer, whichis an invisible layer on top
`of each worksheet.
`
`a \
`
`The draw layer is discussed in Chapter 14. This chapteris
`concerned only with data that you enter into worksheetcells.
`
`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 enterinto cells can be used in
`formulas or can be used to provide the data that is used to
`
`/
`a i
`
`+
`
`+
`
`+
`
`+
`
`In This Chapter
`
`Types of Worksheet
`Data
`
`Entering Values
`
`Entering Text
`
`Dates and Times
`
`Changing or Erasing
`Values andText
`
`Formatting Values
`
`Formatting Numbers
`Using Shortcut Keys
`
`Basic Cell Formatting
`
`Data Entry Tips
`
`+
`
`+
`
`+
`
`+
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 2 of 17
`Exhibit 2014/ Page 2 of 17
`
`

`

`
`
`
`
`
`
`94
`
`Part Il # Introductory Concepts
`
`create a chart. Values also can be dates (such as 6/9/2019) or times (such as 3:24
`a.m.), and you'll see that you can manipulate these types of values quite efficiently.
`
`Figure 6-1 shows a worksheet with some values entered In It.
`
`
`
`Figure 6-1: Values entered in a worksheet
`
`Text
`Most worksheets also include non-numeric text In someof their cells. You can
`insert text to serve as labels for values, headings for columns,or Instructions about
`the worksheet. Text that begins with a number Is still considered text. For example,
`if you enter an address such as 1425 Main St. into a cell, Excel considers this to be
`text rather than a value.
`
`Figure 6-2 shows a worksheet with text in someof the cells. In this case, the text Is
`used to clarify what the values mean.
`
`Loan Stan Date
`Loan Arourt
`Loan Term (fears)
`lsterest Rate
`
`120
`FO
`x
`0.0%
`
`Total Payments
`
`Marthly Pageant
`
`Figure 6-2: This worksheet consists of text and values.
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 3 of 17
`Exhibit 2014/ Page 3 of 17
`
`

`

`
`
`Chapter 6 + Enteringand EditingWorksheet Data
`
`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 powerful formulas that use the values (or even text) in cells to calculate a
`result. When you enter a formula into a cell, the formula's result appears tn the cell.
`If you change any of the values used by a formula, the formula recalculates and
`shows the new result. Figure 6-3 shows a worksheet with values, text, and formulas.
`
`- Cross- )
`
`Chapter9discussesformulasin detail.
`
`pt)
`
`lterest Rate
`
`Morthly Payymnt=§ 204568
`Tota Payments
`
`$73580566 2s
`
`I
`
`Figure 6-3: Cells 88 and 89 contain formulas that use the other values.
`
`Excel’s Numerical Limitations
`
`New users often are curious aboutthetypes ofvalues that Excel can handle. In other words,
`how large can numbers be? And how accurate are large numbers?
`Excel's numbers are precise up to 15 digits. For example, if you enter a large value, such as
`123,123,123,123,123,123 (18 digits), Excel actually stores it with only 15 digits of precision:
`123,123,123,123,123,000. This may seem quite limiting, but in practice, it rarely causes any
`
`Here are some of Excel's other numerical limits:
`
`Smallest negative number:—9.9E+307
`Smallest positive number: 1£-307
`
`Largest negative number:— 1£-307
`These numbers are expressed in scientific notation. For example, the largest positive
`number is “9.9 times 10 to the 307th power.”
`
`
`
`
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 4 of 17
`Exhibit 2014/ Page 4 of 17
`
`

`

`
`
`118
`
`Part il + Introductory Concepts
`
`Forcing a NewLinein a Cell
`If you have lengthy text tn a cell, you can force Excel to displayit In multiple lines
`within the cell. Use Alt+Enter to start a new line In a cell. Figure 6-14 shows an
`example of text In a cell that ts displayed tn multiple lines. When you add a line
`break, Excel automatically changes the cell's format to Wrap Text.
`
`- Cross- )
`
`Learn moreabouttheWrapTextformattingfeatureinChapter 11.
`
` jsheetz2le
`
`1938
`as
`Per Origheal Per Plan?
`Budget Model Budget
`669233
`061581
`STB232
`722,790
`117001
`138,751
`
`Income
`Experses
`Net
`
`Figure 6-14: Alt+Enter enables you to force a line break
`in a cell.
`
`Entering Fractions
`If you want Excel to enter a fraction into a cell, leave a space between the whole
`numberand the fraction. For example, to enter the decimal equivalent of 67/s,
`enter 6 7/8 and then press Enter. When you select the cell, 6.875 appears tn the
`formulabar, and the cell entry appears as a fraction. If you have a fraction only
`(for example, '/s), you must entera zero first, like this: 0 1/8. When you select the
`cell and look at the formula bar, you see 0.125. In the cell, you see '/s
`
`Using a Data Entry Form
`If you're entering data that Is arranged In rows, you mayfind It easier to use Excel’s
`built-in data form for data entry. Figure 6-15 shows an example of this.
`
`
`
`
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 5 of 17
`Exhibit 2014/ Page 5 of 17
`
`

`

`
`
`126 Part Il + IntroductoryConcepts
`
`Hiding and Unhiding a Worksheet
`In some cases, you may want to hide a worksheet. Hiding a worksheet Is useful tf
`you don’t want others to see it or just wantto get It out of the way. When a sheet Is
`hidden, Its sheet tab Is hidden also.
`
`To hide a worksheet, choose Format © Sheet © Hide. The active worksheet (or
`selected worksheets) will be hidden from view. Every workbook musthave 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 ® Sheet > Unhide. Excel opens a
`dialog boxthat lists all hidden sheets. Choose the sheet that you want to redisplay
`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 wantto redisplay.
`
`Zooming Worksheets
`Excel enables you to scale the size of your worksheets. Normally, everything you
`see onscreen Is displayed at 100 percent. You can change the “zoom percentage”
`from 10 percent (very tiny) to 400 percent (huge). Using a small zoom percentage
`can help youto get a bird’s-eye view of your worksheet, to see how It’s lald out.
`Zoomingtn ts useful If your eyesight isn't quite what It used to be and you have
`trouble deciphering those 8-pointsales figures. Figure 7-4 shows a window zoomed
`to 10 percent and a window zoomed to 400 percent.
`
`
`
`: Awindow zoomed to 10 percent and a window zoomed to 400 percent.
`
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 6 of 17
`Exhibit 2014/ Page 6 of 17
`
`

`

`
`
`128 Part Il # Introductory Concepts
`
`ss )
`
`Ifyourworksheetusesnamedranges(refertoChapter8),you'llfindthatzooming
`
`your worksheet to 39 percent or less displays the name of the range overlaid on
`the cells. This is useful for getting an overview of how a worksheet is laid out.
`
`If you're using a Microsoft Intell{Mouse (or a compatible wheel mouse), you can
`change the zoom factor by pressing Ctrl while you spin the mouse wheel. Each spin
`changes the zoom factor by 15 percent (but you can't zoom out more than 100
`percent). If you find that you doalot of zooming In, you can change the default
`behavior for the mouse wheel from scrolling to zooming. To change the default,
`select Tools Options, click the General tab, and then select the Zoom on roll with
`IntelliMouse check box. After you make this change, you can zoom by spinning the
`wheel and you won't haveto pressCtrl.
`
`Views, Split Sheets, and Frozen Panes
`
`As you add more information to a worksheet, you mayfind that It gets more
`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 elther 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 Window > New Window. Excel
`displays a new window with the active workbook, similar to Figure 7-7. Notice the
`text In the windows’title bars: Budget.xls:1 and Budget.xls-2.
`
`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 ts 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 dothis.
`
`
`
`
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 7 of 17
`Exhibit 2014/ Page 7 of 17
`
`

`

`
`
`
`
`129
`
`Chapter7 # Eseential SpreadsheetOperations
`
`
`
`Figure 7-8: Displaying three worksheets in the same workbook.
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 8 of 17
`Exhibit 2014/ Page 8 of 17
`
`

`

`130
`
`Part Il # Introductory Concepts
`
`Splitting Panes
`If you prefer not to clutter your screen with additional windows, Excel provides
`another option for viewing multiple parts of the same worksheet. The Window>
`Split commandsplits the active worksheet into two or four separate panes. The
`split occurs at the location of the cell pointer. You can use the mouse to drag the
`individual panes to resize them.
`
`Figure 7-9 shows a worksheet split into four 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 of 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 panes,
`choose Window > RemoveSplit.
`
`Kovember Oecamber
`10
`6&9
`=D
`400
`178
`472
`495
`775
`cea
`5
`621
`
`28
`
`ia 5
`
`6
`563
`wa
`561
`7t8
`a4
`500
`303
`22
`
`Tip
`
`Another way to split and unsplit panes is to drag either the vertical or horizontal
`> split bar; when you move the mouse pointer over a split bar, the mouse point
`=
`changes to a pair of parallel lines with arrows pointing outward from each line.
`Figure 7-10 shows where these split bars are located. To remove split panes by
`using the mouse, drag the pane separatorall the way to the edge of the window,
`or just double-click it.
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 9 of 17
`Exhibit 2014/ Page 9 of 17
`
`
`
`
`
`

`

`
`
`
`
`
`
`Chapter 10 # UsingWorksheet Functions 217
`
`IF
`The IF function ts one of the most importantof all functions. This function can give
`your formulas decision-making capability.
`
`The IF function takes three arguments. The first argument Is a logical test that must
`return elther TRUE or FALSE. The second argument ts the result that you want the
`formula to display tf the first argument Is TRUE. The third argument ts the result
`that you want the formula to display tf the first argument Is FALSE.
`
`In the example that follows, the formula returns Positive If the value tn cell Al Is
`greater than zero, and returns Negative otherwise:
`
`~IF(AL>O,"Positive”, "Negative”)
`
`Notice that the first argument (Al>0) evaluates to logical TRUE or FALSE. This
`formula has a problem tn that It returns the text Negative if the cell ts blank or
`contains 0. The solution Is to use a nested IF function to perform another logical
`test. The revised formula Is as follows:
`
`~IF(AL>0,"Positive”, IF(A1<0, "Negative", "Zero" ))
`
`The formula looks complicated, but when you break It down, you see that It’s rather
`simple. Here’s how the logic works. If Al ts greater than 0, the formula displays
`Positive, and nothing else Is evaluated. If Al Is not greater than zero, however, the
`second argumentIs evaluated. The second argument Is as follows:
`
`IF(A1<O0, "Negative", Zero”)
`
`This ts simply another IF statement that performs the test on Al again. If It’s less
`than 0, the formula returns Negative. Otherwise,it returns Zero. You can nestIF
`statements as deeply as you need to—although It can get very confusing after
`three or four levels.
`
`Using nested IF functions Is quite common, so understanding how this concept
`works Is In your best Interest. Mastering IF will definitely help you to create more
`powerful formulas.
`
`Figure 10-7 shows an example of using the IF function to calculate sales commis-
`sions. In this example, the usual commission rate ts 5.5 percent of sales.If the total
`sales of a sales rep exceeds the sales goal, the commission rate Is 6.25 percent. The
`formula In cell C6, shown next, uses the IF function to make a decision regarding
`which commission rate to use based on the sales amount:
`
`~ IF (B6>-SalesGoal ,B6*BonusRate, B6*CommissionRate)
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 10 of 17
`Exhibit 2014/ Page 10 of 17
`
`

`

`
`
`Chapter 11 # Worksheet Formatting 245
`
`Shrink to Fit
`Excel includes a Shrink to fit option, which reduces the size of the text so thatIt fits
`into the cell without spilling over to the next cell.
`
`If you apply wraptext formatting to a cell, you can’t use the shrink-to-fit formatting.
`
`Merging Cells
`When you merge cells, you don't combinethe contents of cells. Rather, you
`combine a group of cells that occupy the same spaceInto a single cell. Figure 11-9
`shows two sets of merged cells. Range C3:G3 has been merged Into a single cell that
`holds the table’s title. Range B5:B9 has also been merged to hold a title for the
`table’s rows.
`
`| Fon aling Examples. sie sealps
`lola~/ol>=[w=
`
`4
`
`
`‘ Dimers:Rignet[nitektsioe
`
`Figure 11-9: The titles for this table appear in merged cells.
`
`You can merge any numberof cells, occupying any numberof rows and columns.
`However, the range that you Intend to merge should be empty, except for the upper-
`left cell. If any of 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. The only way to “unmerge” cells Is to use the
`Format 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 specify an angle. To change the
`orlentation, select the cell or range, open the FormatCells dialog box, and select
`the Alignmenttab. 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
`
`

`

`
`
`Chapter 11 Worksheet Formatting 247
`
`Another Type ofJustification
`
`Excel provides another way to justify text, using its Edit+ Fill+Justify command. This com-
`mand has nothing to do with the alignment options discussed in this chapter. The
`Edit> Fill +Justify command is useful for rearranging text in cells so that it fits in a specified
`range. For example, you may import a text file that has very long lines of text.
`You easily can justify this text so that it’s displayed in narrower lines. The accompanying
`figure shows a range of text before and after using the Edit> Fill>Justify command.
`
`
`hi hicrosot Escal, a workbook
`
`the fie in which you work and
`
`
`store your data Because each
`okbook can certain many
`
`Sheets yOu Can Organize warous
`kinds of relsted information in a
`single the Use worksheets to
`tel and analyze das. You ca
`
`
`
`erter and echt data on sewn
`OS
`
`
`‘cimets simultaneous9Eee
`
`
`
`In Microsoft Excel, a work book is the fle mn which you work ond
`
`eh you create a chan,
`
`S10fe yOu! dala. Because each workbook can Conan many sheets,
`
`
`you can organize venous hinds of relsted information na single file.
`a) can place the chwt on thr
`orkshact wath its ralatedd
`
`
`
`Use workshwats to list and analyze data You can anter and adt
`
`
`
`5
`dala on several workeheets simuksrmoush and perkern cacukwions
`
`
`based on data from muttple worksheets. When you create a chant,
`
`
`you Can place the Chart on the workshwet with its relited data
`|
`
`
`
`This command works with text in a single column. It essentially redistributes the text in the
`cells so that itfits into a specified range. You can make the text either wider (so that it uses
`fewer rows) or narrower (so that it uses more rows).
`Select the cells that you want to justify (all in one column) and then extend the selection to
`the right so that the selection is as wide as you want the end result to be. Choose
`Edit+ Fill >Justify, and Excel redistributes the text.
`Blank rows serve as paragraph markers. If the range that you select isn’t large enough to
`hold all the text, Excel warns you and allows you to continue or abort. Be careful, because
`justified text overwrites anything that gets in its way.
`
`
`
`
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 12 of 17
`Exhibit 2014/ Page 12 of 17
`
`

`

`
`
`
`
`
`
`Working with
`Lists
`
`fa. 4
`i)
`
`-
`
`+
`
`e+
`
`+
`
`In This Chapter
`
`frequently used to managelists, or worksheet databases.
`
`IsaList?
`RecconductedbyMicrosoft Indicatesthat Excelts
`This chapter covers list management and demonstrates useful —Gan,Nev Do
`techniques that involvelists.
`a
`Designing a List
`Entoring Dota into0
`
`*
`
`WhatIs a List?
`
`A list ls essentially an organized collection of information.
`More specifically, a list consists of a row of headers (descrip-
`tive text), followed by additional rows of data, which can be
`values or text. You may recognize this as a database table—
`which ts exactly what It ts. Beginning with Excel 5, Microsoft
`uses the term /isf to refer to a database stored in a worksheet
`and the term database to refer to a table of information stored
`in an externalfile. To avoid confusion, | adhere to Microsoft's
`terminology.
`~ Cross- \
`| Reference)
`
`°
`| cover external database files in Chapter 24.
`
`Filtering a List
`
`Using Database
`Functions with Lists
`
`Sorting a List
`
`Creating Subtotals
`
`-~
`
`+
`
`©
`
`+
`
`Figure 23-1 shows an example of a list in a worksheet. This
`particular list has tts headers tn row | and has 10 rows of data.
`The list occupies four columns. Notice that the data consists
`of several different types: text, values, and dates. Column C
`contains a formula that calculates the monthly salary from the
`value in columnB.
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 13 of 17
`Exhibit 2014/ Page 13 of 17
`
`

`

`
`
`512 Parti + Analyzing Data
`
`> ie
`
`a7AI
`
`New York
`2,48 Ancona
`S650 New York
`1,654 Minnesttas
`3,750 Anzona
`4,333 New York
`4.042 New York
`2,042 Minnescts
`4,700 Arizona
`6.250 Mnnescts
`
`2183
`4604
`104263
`1005
`22800
`S264
`112182
`a4eo
`alLer
`
`People often refer to the columns In a list as fields and to the rows as records. Using
`this terminology, the list shown In thefigure has five fields (Name, Annual Salary,
`Monthly Salary, Location, and Date Hired) and ten records.
`
`The size of the lists that you develop tn Excel ts limited by the size of a single work-
`sheet. In other words,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 maynotbe possible. At the
`other extreme,a list can consist of a single cell—not very useful, butIt’sstill
`considered a list.
`
`In versions of Excel prior to Excel 97, a list was limited to 16,383 records.
`
`Note
`
`™~s
`
`What Can You Do with a List?
`
`Excel provides several tools to help you manage and manipulatelists. 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:
`
`+ Entering datainto thelist
`
`Filtering the list to display only the rows that meet certain criteria
`
`Sorting the list
`
`Inserting formulas to calculate subtotals
`
`+ Creating formulas to calculate results on the list filtered by certain criteria
`
`+ Creating a summary table of the data In thelist (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
`
`

`

`
`
`New
`Exbiry
`New
`
`Exbtry
`New
`New
`New
`
`Frank
`Franks
`Franke
`Franke
`Jentins
`Jeanine
`
`Jantire
`Jentins
`Jeanine
`Jentins
`Jantire
`Paterson
`
`Rekinace
`
`Paterson
`Robinson
`Robinson
`
`Figure 23-23: Excel added the subtotal formulas automatically— and
`even created an outline.
`
`Summary
`
`In this chapter, | discusslists. A list is simply a database table that Is stored ona
`worksheet. Thefirst row of the list (the header row) contains fleld names, and
`subsequentrows contain data (records). | offer some pointers on data entry and
`discuss two ways to filter a list to show only rows that meet certain criteria.
`AutoFiltering Is adequate for manytasks, butIf your filtering needs are more com-
`plex, you need to use advancedfiltering. | end the chapter with a discussion of
`sorting and Excel's automatic subtotal feature.
`
`+
`
`+
`
`+
`
`
`
`
`
`IPR 2018-00044
`IPR 2018-00044
`Exhibit 2014/ Page 15 of 17
`Exhibit 2014/ Page 15 of 17
`
`

`

`
`
`552 Part IV # Analyzing Data
`
`+ Name: The nameof the external data range. You can change this name or use
`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.
`
`+ 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
`store the password so that you don't need to enter It when you refresh the
`query.
`
`+ Refresh control: Determines how and when Excel refreshes the data.
`
`+ Data formatting and layout: 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 tn the title bar and then click an option In the dialog box.
`
`You can manipulate data returned from a query just like any other worksheet range.
`For example, you can sort the data, formatIt, or create formulas that use the data.
`
`‘In prior versions of Excel, if you intend to refresh the query, you need to keep the
`external data range intact. That is, you can't insert new rows or columns in the
`external data range, because refreshing the query causes the external range to be
`rewritten. Similarly, you lose any formatting that you applied to the external data
`range when you refresh the query.
`
`In Excel 2000, refreshing a query does not overwrite the external data range. You
`are free to format the external data range or insert rows and columns. You also can
`include formulas in those rows and columns that refer to other parts of the exter-
`nal data range. Your work will not be destroyed when yourefresh the query.
`
`Refreshing a Query
`After performing a query, you can save the file and then retrieve It later. Thefile
`contains the data that you originally retrieved from the external database. The
`external database may have changed, however,In the Interim.
`
`If you checked the Save query definition option in the External Data Range Properties
`dialog box, then Excel saves the query definition with the workbook. Simply move the
`cell pointer anywhere within the external data table in the worksheet and then use
`one of the following methods to refresh the query:
`
`Right-click and select Refresh Data from the shortcut menu
`+ Select Data Refresh Data
`
`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
`
`

`

`
`
`
`
`Chapter 24 # Using External Database Files 553
`
`Excel launches Query and uses your original query to bring In the current data from
`the external database.
`
`Tip
`
`If you find that refreshing the query causes undesirable results, use Excel’s Undo
`Q featureto “unrefresh”the data.
`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 performing a query, you may want to copy or move the external data range,
`which you can do by using the normal copy, cut, and paste techniques. However,
`make sure that you copy orcut the entire external data range—otherwise, the
`underlying query ts not copied, and the copied data cannot be refreshed.
`
`Deleting a Query
`If 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 > Delete.
`
`Ifyou simply press Delete, thecontentsofthecells areerased, butthe underlying
`query remains. Excel displays a dialog box asking whether you want to delete the
`query. If you choose No, you can refresh the query, and the deleted cells appear
`again, including any formatting that you applied to them.
`
`<a
`“sil
`
`When you refresh, Query returns only data that is retrieved from the external
`database. If you delete rows or columns that you inserted into the external data
`range, Query does not redisplay those rows and columns when you refresh.
`
`Changing Your Query
`If you bring the query results into your worksheet and discover that you don’t have
`what you want, you can modify the query. Movethe cell pointer anywhere within
`the external data table in the worksheet and then use oneof the following methods
`to refresh the query:
`
`Right-click and select Edit Query from the shortcut menu
`
`+ Select Data Get External Data Edit Query
`
`+ Click 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
`
`