Wiley Microsoft Office Excel 2007 Data Analysis
Contents
1. 3 13 CUT AND PASTE Select the cells you want to move Click the Home tab Click the Cut button in the Clipboard group A dotted line appears around the selected cells Place the mouse pointer where you want to paste the cells Click Paste in the Clipboard group Excel places the data in the new location Cut Copy and Pastexlsx Microsg J Jaidey e e January February March Region 1 100 345 186 386 152 862 Region 2 169 122 197 980 110 749 Region 3 166 274 108 587 122 681 146 132 162 663 114 330 Q z Ga U ct 3 a A Cut Copy and Faste xlss Microso Deka Review Vice Dewd oor January February 100 345 186 386 152 862 10 Region2 169 122 197 980 110 749 11 Region3 166 274 108 587 122 681 12 Reg 146 132 162 663 114 330 To use your mouse to move a range of cells select the cells you want to move and then point to the border of your selection When your mouse pointer turns to a amp drag your selection to a new location To use your mouse to copy a range of cells select the cells you want to copy and then hold down the Ctrl key while you point to the border of your selection When your mouse pointer turns to a drag your selection to a new location You can select cells and press Ctrl C to copy or Ctrl X to cut and then press Ctrl V to paste When you cut or copy a range of cells that have hidden rows or columns and then paste Excel in2. Car 3 8 449 1 014 9 463 5 000 4 463 4 4 463 537 7 5 000 5 000 Numbers have been rounded Excel applies a fill color to the cells CENEL Apply Formats xlsx Microsoft Excel you selected Hame ingert Page Layout henereilers Data Review View Beld bis 5 Calibri dJa AC i General x ea Coico Formesting S insert Ca o i 7 ee Ja Format as Table F Delete CHANGE THE TEXT COLOR q ee O 3S H am G ye FH Forat gt H lm B Click and drag to select the cells you want to format 2 Click here and select a font color Car Numbers h 10 Feinl vubomatic Theme Colors Standard Colors Ia Cells H ia Hurnkes Myles G w More Cols 1 515 186 1 822 17 008 2 12 008 1 441 13 449 5 000 8 449 3 8449 1 014 9 463 5 000 4 463 4 4 463 537 5 000 5 000 ave been rounded F a Excel applies a font color to the cells you selected ADD A BORDER Click and drag to select the cells you want to format rd Fi Fi Loan Sched Click the down arrow next to the Borders button Fl A ai a Click here and select a color Car Fl ld Bl de Fi Click to select a border style Numbers Hame Imei Page Leyeut F mubas Dala Review View Add Ins a i g a e u i heli a lc atkei 2 J Fl Apply Formats xlsx Microsoft Excel General i a
3. peer Heer an a a i a Number G a Inert FE Delete E Format 7 Cel IEJ Conchtionel Ponmetting Hig Fornet ss Table E Cel Styles Styles Re iE ik Bottom Sorger Teg Bander Lat Border Right Gorder Mo Border Al Borders lt Q Cutside Eorders Thick Box Border 2 End Totalan Repaymentin Balance Bottom Double Border P UO i JOU i Hil Thick Fotior Border 41 13 449 3 000 6 449 Top and Bottora Border 14 9 463 5 000 A 463 37 5 000 3 000 Top and Thick Bottern Border Top and Double Bottom Border Excel adds a border to your cells Loan Schedule Bander gi Draw Dorde He Drs Border Eril d Dese Dorde E EE mlie ole rli a poe Line Sle b FE Mare Borders E g 5 Year ing of Year est at 12 End Totalan Repaymentan Balance Numbers have been rounded Car You can set the default font and font size for all of your workbooks Click the Office button A menu appears Click Excel Options in the lower right corner The Excel Options dialog box appears Click Popular In the Use this Font field select the font you want to use In the Font Size field select the font size you want to use Click OK The next time you open a workbook it will use the font and font size you selected If you want text to stand out you can change the font
4. 197 980 110 749 Region 3 166 274 108 587 122 681 146 132 162 663 114 330 You can delete the contents of cells by selecting the cells and then pressing the Delete key You can also use Excel s Clear options to remove everything or to delete formats contents or comments from a cell To remove everything from a cell or group of cells select the cells and then click the Home tab Click Clear 2 in the Editing group and then click Clear All To remove formats while leaving the contents intact select the cells and then click the Home tab Click Clear in the Editing group and then click Clear Formats To remove contents while leaving the formatting intact select the cells and then click the Home tab Click Clear in the Editing group and then click Clear Contents You can use comments to annotate your worksheet To add a comment click the cell to which you want to add a comment click the Review tab and then click Comment You can then type your comment in the block provided To remove a comment select the cell with the comment click the Home tab click Clear and then click Clear Comments To learn more about comments see Chapter 11 J Jaidey e e Q z Ga U ct 3 a A Find and Replace you want can be difficult You can use Excel s Find feature to locate information If you want to replace the found information with new information use Excel s Find and Replace feature Use the Find tab in th
5. 36 340 70 224 box click Options if your dialog pte ee ee PeT box does not look like the one 31315 21781 rma Peake shown here 17 163 24 368 24 830 O lra 34 703 41 010 35 453 b Format Note The Options button allows you 19 681 12 280 b Chante Format From Cell to toggle between the short and a 13 922 27 349 Clear Find Format 17 748 44 178 long forms of the dialog box 12 751 Click here and select Choose Format From Cell Click in a cell that has the format you want to replace 33 133 276 207 304 284 373 839 29 680 Click Close A preview of the format 9419 35 360 40 681 85 460 you selected appears 8 31 197 28 982 105 085 9 988 36 340 Find and Replace Click here and select Choose rover oS 20 038 il bp 3 g E f 5 Fired what A Format From Cell TET 21 781 iN a7 Click in a cell that has the 17 163 24 308 24830 eo ae format t to use as a voce 12380 RRR oc Deor a aee ormat you want to u 19 681 12 280 TTZ Lokin Farmuas w Options lt lt replacement 9 923 13 922 49 Farmucs 17 748 DEE gt PF A preview of the format you 12 751 33 133 29 680 selected appears 276 207 304 284 373 839 Click Replace All Find and Replace xlsx M You can click Replace to ayout Formulas Data Review make one change at a time If you want to find instead of replace formats click Find All or
6. 5 000 4 463 Year End Beginning Interest Year End Loan Loan Year ofYear at12 Total Repayment Balance 1 15 186 1 822 17 008 5 000 12 008 2 12 008 1 441 13 449 5 000 8 449 G 3 8 449 1 014 9 463 5 000 4 463 4 4463 537 5 000 5 000 Numbers have been rounded Excel has several buttons you can use to align data within a cell Use the Align Left button E to align your data with the left side of the cell use the Align Right button 3 to align data with the right side of the cell and use the Center button to center data in the cell F Excel has buttons you can use to place data at the top bottom or middle of the cell Use the Top Align button to place data at the top of the cell use the Middle Align button to place data in the middle of the cell and use the Bottom Align button 2 to place data at the bottom of the cell To set data off you may want to put it in a cell but indent it You can use the Increase Indent button to increase the amount of the indent in a field and the Decrease Indent button to decrease the amount of the indent in a field mCi e e Q z Ga U et 3 c A Select DEE efore you can execute an Excel command you must select the cells to which you want the command to apply For example if you want to add a blue fill to several columns of cells you start the process by selecting the cells The mo
7. Corton Formatting erte E Click the Home tab Ja le lee A Click Find amp Select in the Editing group 7 E Geo A menu appears m a E Se Te Sci ae Sales 2008 piir Click Find January February March E EE 2008 2008 2008 er Region1 100 345 186 386 152 862 oad Region2 169 122 197 980 110 749 Region3 166 274 108 587 122 681 Region4 146 132 162 663 114 330 581 873 655 616 500 622 The Find and Replace dialog box appears 4 Type what you want to find into the Find What field Click Find All to find all instances Click Find Next to find the first instance This example uses Find All 6 lf you clicked Find All click an instance to move to that instance Excel moves to the instance you clicked 22 2008 100 345 169 122 166 274 146 132 581 873 Sales 2008 January February 2008 136 86 197 980 108 587 162 663 655 616 March 2008 gt Find and Replace Find i Replace ag at Prod whs z Seeks found Shee Shectl Fid ane aplara she Pind anc Seclecs abs Frd am Pepher eons Nare Cell the fcf2 remsy 2000 Shc 3 Wach200e Ckse Formia REPLACE Q Repeat Steps 1 to 3 under Find January 2008 100 345 169 122 166 274 146 132 581 873 In the Find and Replace dialog box click the Replace tab Enter what you want to find Enter your replacement Click th
8. Place pmm i 1 7 ER _ LY ea a button each click adds a decimal place CURRENCY FORMAT D 133 559 Select the numbers to format Format Cells Fow Ewds F Protection Click the Number group s e launcher 3 iar 7 ETEN ss Decimal places o lt Q Samba In the Number tab of the Format Cells r tesdvsnines dialog box click Currency Click here and set the number of decimal places Durey femnat arc used for genea monetary values Use Socounting Ponas to align cedna panis in a ca unin Click here and select a currency symbol Click to select a number format for negative numbers Click OK oo OG Excel formats your numbers Changing a number format You can use the Text format in the If you right click in any cell can increase the contents of Format Cells dialog box to convert that contains a number the cell If your number is too a number to text Numbers you can choose number long to fit in its cell Excel fills formatted as text are not used in formatting options from the the cell with pound signs mathematical calculations Certain mini toolbar or you can To view the number numbers for example employee click Format Cells from the double click the line at the top numbers are never used in context menu to open the of the column that separates mathematical calculations and Format Cells dialog box columns or click and drag the should be formatt
9. Region 3 to accept the default and 1 119 873 136 876 129 481 remove the menu 101 050 147 294 148 681 187 520 189 405 123 751 186 488 178 615 101 020 652 190 502 933 You can set the following options by clicking the Options button in the Clipboard task pane OPTION DESCRIPTION Show Office Clipboard Automatically Shows the Office Clipboard automatically when you copy Show Office Clipboard When Ctrl C Pressed Twice Shows the Office Clipboard when you press Ctrl C twice Collect Without Showing Office Clipboard Prevents the Clipboard task pane from appearing while you are copying When the Office Clipboard is active displays an icon on the Windows taskbar Show Status Near Taskbar When Copying Shows the number of items collected out of 24 when you add an item to the Office Clipboard Insert and Delete Cells s you develop your worksheets you will A sometimes want to make changes to the layout For example as you modify your worksheet you may find that you need to insert or delete cells or even insert or delete entire rows or columns of cells In Excel you can shift a cell or group of cells up down left or right You can also add or delete rows and columns When you insert cells rows or columns Excel automatically adjusts any formulas that reference the cells whether they are relative or absolute See Appendix C to learn more about relative and absolute cell references When
10. Find Next to highlight Replace with w within he Sheet w L Match case ee vrom il match entia cell contents Low in Formulas e cells in the worksheet 11 without replacing formats aa Sales Sales Sales s Excel replaces the formats 9419 35360 40681 5 Kuudi A message box appears telling you Excel made replacements Click OK 34 703 2 J Excel has completed its search end hes made replacements 36 340 20 033 _ ok 10 34 909 107 144 21 781 92 752 24 830 66 361 35 453 19 681 2so 13 695 45 656 9 923 13 922 27 349 31 194 On the Home tab when you click Find amp Select in the Editing group Excel presents a menu of options If you click Formulas Comments Conditional Formatting Constants or Data Validation Excel finds all the formulas comments conditional formatting constants or data validations in your worksheet and selects them You can use the Tab key and Shift Tab keys to move among the cells If you want to move around your worksheet quickly you can use the Go To dialog box Press Ctrl G or click the Home tab click Find amp Select in the Editing group and then click Go To to open the Go To dialog box In the Go To field you can double click a range name to move to the named range In the Reference field type a cell address and then click OK to move to a cell J Jaideyp e e Q z Ga U et 3 a A
11. Formwlas Osta Review View asda E Conditional Formetting F Insert 7 J Format ss Table Cell Styles U Calibri 20 z ja a ay 9 Bie waa E Loan Schedule Year ing of Year est at 12 End Totalan Repayment an Balance 15 186 8 7 008 S 000 008 2 12 008 1 441 13 449 5 000 8 449 8 449 1 014 9 463 5 000 4 463 a 4 537 5 000 5o00 Numbers nave been rounded Ei Conditional Formatting 3 Insert ie Format as Table 3 gt Delete E Cell Styles E Format eee Excel wraps your text je Ds e Apply Formats xlsx Microsoft Excel Home Insert Page Layout Formulas Data Review Views Add Ins ORIENTATION ea Sean 2 see l BU S E ae A Angle Counterclockwise Click the cell or cells whose kal E L an iF fi Alig Orientation you want to change 3 c E verica Tert l 2 k atate Text Up Click here and select an orientation Excel changes the orientation of your text KAS 5 Rotate Text Down Loan Format Cell Alignment Balance at Year End Beginning Interest Year End Loan Loan 1 15 186 5 1 822 2 12 008 1 441 Loan Schedule Loan Balance at Year ofYear at12 Total Repayment Balance 17 008 13 449 ea 9 463 aged 5 000 12 008 5 000 8 449
12. If you want to adjust the size of your text so it fits in a cell you can adjust the font size You can click the down arrow next to the Font field to change the font for the selected range Click the down arrow next to the Font Size field to change the font size in the selected range You can also right click and then use the mini toolbar to change the font and font size continued gt mCi e e Q z Ga V et 3 a A Format Cells continued f the text you enter is too long to fit in a single cell can center text within a cell by using the Center button Excel allows the text to spill over into an adjacent To center text across several cells you can use the Merge cell If you place text or data in the adjacent cell and Center button In addition to being able to merge and Excel cuts off the text in the original cell and you cannot Center you can merge cells in Excel 2007 without see all of it If you want to display the text in the original centering and you can merge several rows and columns cell on multiple lines in a single cell use the Excel Wrap Of cells into a single cell If you want to return merged Text feature By default data or text you enter in a cell displays cells to their original state you can select the cells and then click the Unmerge Cells option from left to right You can change this by clicking the The Excel Ribbon also has several options you can use to Orientation button
13. Introduction to Data Analysis with Excel T his book is about using Microsoft Excel to analyze your data Microsoft Excel is an electronic worksheet you can use to maintain lists perform mathematical financial and statistical calculations create charts analyze your data with a PivotTable and much more Excel can help you locate data find trends in your data and present your data to others Each Excel file is a workbook Each workbook can have multiple worksheets Worksheets are made up of rows and columns of cells you use to enter information One of the many useful features of Excel is the ability to calculate When you enter a formula into Excel Excel can automatically calculate the result and when you make changes to your worksheet Excel can automatically recalculate You can also use Excel to create charts A chart is a graphical representation of your data When using Excel Introduction to Data Analysis with Excel you can choose from several types of charts including Column Line Pie Bar Area and Scatter Charts can make your data easier to read easier to understand and easier to compare A PivotTable is an interactive worksheet table you can use to analyze data A PivotTable gives you an easy way to summarize and view large amounts of data Using a PivotTable you can rotate rows and columns of data so you can see different views of your data easily You can use Excel to create PivotTables Excel provi
14. and selecting a new orientation align text within a cell You can align text with the top You can angle your text or show your text vertically middle or bottom of a cell and or with the left right or Titles provide a brief summary of your data and you may center of a cell want to center them over the data they summarize You Format Cells continued MERGE AND CENTER Click the Home tab Click and drag to select the cells you want to merge and center Click the Merge and Center button in the Alignment group Excel merges and centers your text WRAP TEXT Click and drag to select the cells whose text you want to wrap Click the Wrap Text button in the Alignment group 12 CEE i 7 Apply Formats xlsx Microsoft Excel Hame Insert Page Layout Forrailas Data Review Views Add Ins ai z ae e e aa 7 i mat 25 ame T ree ose S55 Tr Cell Siles nl d Ip Fa 1 Styles 4 ZM Insert General r Ion Loan Schedule Year ing of Year est at 12 End Totalan Repaymentan Balance 1 515 186 1 822 17 008 5 000 12 008 2 12 008 1 441 13 449 5 000 8 449 Car 3 8 449 1014 9 463 5 000 4 463 A 446357 5 000 5 000 Numbers have been rounded dd PG js Apply Formats xlsx Microsoft Excel Home Insert Page Layout
15. any other Microsoft Windows program When you paste to an Excel workbook Excel replaces the content of the cells into which you paste with the cut or copied values For that reason be careful when you paste because you can overwrite other data The best method is to select the first cell into which you want to paste the contents and then apply the Paste command Copy Cut and Paste Cells COPY AND PASTE Select the cells you want to copy Click the Home tab Click the Copy button in the Clipboard group A dotted line appears around the copied cells Place the mouse pointer where you want to paste the cells Click Paste in the Clipboard group Excel places a copy of the copied cells in the new location Cut Copy and Pastexlsx Microsoft ale ON J EENE Siment rm Member 100 345 169 122 166 274 146 132 186 386 197 980 108 587 162 663 152 862 110 749 122 681 114 330 iatbn fo gt na see eta ajaa eea eaa Fm get F nofio ta aa Numer B rae y Ciptead 3 1 es January February March 2 Region 1 100 345 186 386 152 862 3 Region 2 169 122 197 980 110 749 4 Region 3 166 274 108 587 122 681 5 Region 4 146 132 162 663 114 330 6 7 4 January February March 9 Region1 100 345 186 386 152 862 10 Region 2 169 122 197 980 110 749 11 Region 3 166 274 108 587 122 681 12 Reg 146 132 162 663 114 330
16. cludes the hidden rows and or columns when it pastes If you want to copy only visible cells select the cells you want to copy Click the Home tab Click Find amp Select in the Editing group A menu appears Click Go To Special The Go To Special dialog box appears Click Visible Cells Only Click OK Press Ctrl C Move to the Paste area Press Ctrl V Copy with the Office Clipboard ith Office 2007 you can place content into a WW sec area called the Clipboard and then paste the content into Excel or another Office application Cut and copied content stays on the Clipboard until you close all Office applications The Office Clipboard can store up to 24 cut or copied items When you add the 25th item Office deletes the first item You can store text and graphics on the Clipboard As you add items to the Clipboard they appear at the top of the Clipboard task pane All the items on the Clipboard are available for you to paste to a new location in Excel or into another Office document The Clipboard is not visible until you access it In Excel you access the Clipboard by clicking the launcher in the Clipboard group of the Home tab Each item on the Clipboard appears with an icon that tells you the Office application the information originated from and shows a portion of the text or a thumbnail if the item is a graphic You can also use the Clipboard to store a range of cells The Office Clipboard pastes the entire range in
17. cluding all the values but any formulas in the cells are not included when you paste You can paste everything on your Clipboard into your worksheet by clicking the Paste All button You can clear the Clipboard by clicking the Clear All button After you paste an item from the Clipboard Excel provides the Paste Options icon menu You can use the menu to choose whether you want to use the source formatting or the destination formatting for the pasted data Copy with the Office Clipboard a Click and drag to select the cells you want to copy Click the Home tab Click the Copy button in the Clipboard group Excel places a copy of the information on the Office Clipboard Click the launcher in the Clipboard group The Clipboard task pane appears mah ie F Copy with the Office Clipboard xlsx Microsoft Excel l mje M Home Insert Page Layout Formulas Data Review View Add Ins a i n Fe Genara da Conditional F i ii Calitei Ja A eile SS E A REEE S Pg j a IE s Me E Format zs Table Paste anf eae p x e eS le IS at me g Bete ie Ore M sa 21 S Cell Styles Choboard la Font E Humber Ta Styles Region 3 129 481 148 681 123 751 101 020 502 933 Region 2 136 876 147 294 189 405 178 615 652 190 101 050 187 520 186 488 594 931 Copy with the Office Clipboard xisx Microsoft Excel Formulas Data Reviews View A
18. dd ins a Er EE E E Home Insert Page Layout i z j General onditi i g Caliber 2 LA x Phi l Conditional Formatti 3 aS 9 on BE Forna as Table Paste esi 7 g Een e g el Cell Styles Humber a Styles 22 3 5 aa Region 3 129 481 148 681 123 751 101 020 502 933 Region 1 z Quarter1 119 873 Quarter2 101 050 a Quarter3 187 520 189 405 186 488 178 615 594 931 652 190 Region 2 5 136 876 147 294 Gimme kera Chck en ten bo peste 5 tegin Region 2 Rage 3Quste L 119 673 E 13 078 izna u s Quarter 4 Click the destination cell 1 of 24 Clipboard x A l Region 2 D Click the item you want SS 136 876 129 481 T to paste re 101 050 147 294 148 681 187 520 189 405 123 51 E 186 488 178 615 101 020 Q 652 190 502 933 D GQ ZN a S The content is pasted into PC a a J o oc o p J e S the new location Region1 Region2 Region 3 n Click an item bo paste In the Paste Options icon eee 2119872 P 16810 3129481 menu choose whether to i aio uart 101 050 147 294 148 681 keep the formatting of the aart 187 520 183 405 123 751 copied item or change it to 186 488 178 615 101 020 match the formatting of the 594 931 652 190 502 933 new location The default is to match the formatting of the new location Press Esc Region1 Region2
19. des a way for you to create and maintain lists A list is a series of rows and columns Each column has a label for example name address telephone number Each row under a column has information pertaining to the column label You can sort filter and analyze your lists in Excel OPEN A NEW WORKBOOK Click the Office button A menu appears Click New The New Workbook dialog box appears Double click Blank Workbook Excel opens a new workbook Microsoall Excel Wats Drew boar Sod Ins Berar 1 Add Commcnischs r i F D aana ey ean t 2 Conditional Funmalchs z 3 ditn Marralen i Set Propertiesclern A fa fal fal a 5 Dookloem 4 Print fiultiple Areas dcx 4a led sevegs Po I Deta Entry Ruksaa 4A E Prnt fuibple Areas size Ta ma Print p D Invcice Templates A Excsanue Rupert Tamplebe daa 5 m Add Cnmmenteeka H E ezici i Dista Entry Rules odes 4a Oats Entry Rules dex 4a E md F O junkaxtse ta 4 Creste a Saldation Let dice Jrctelled Templates bly bermplates Hew From esting Microsoft Office Online herbured Agendas Budgets Calendars Esper reports Forms Jevertories Trmice Lists Plans Planners Purcuase priar Understanding the Excel Window W OFFICE BUTTON Click the Office button to open Save print prepare send publish and close files QUICK ACCESS TOOLBAR Place commands you use often on the Quick Acce
20. e 0 Bp Haase ea a es z B zu H o A EEE PEJ SB Cell styles at The numbers appear in the format you type them Clipboard ry Fant Alignmert F Number Ta Shyles 6 Click and drag to select the cells you want to format Click the Home tab Click the Comma Style button in the Number group Excel separates the thousands in the numbers Excel adds two decimal places Negative numbers appear in parentheses Zeros are represented by a dash Q oa o l r 160502 ere 0 2 512623 312625 0 3 133559 133559 0 4 160 502 00 160 502 00 2 512623 512623 0 E 133559 3355 0 ACCOUNTING NUMBER FORMAT a pileoan Format Numbers Dates and Times xisx Microsatt Fxcel l Home Insert Page Layout Formulas Data Review ie Add Ins D Select the numbers to format amp Calibri a ai a Custom Conditional Formetting g7 Ing me my a 2 F 5 Table g De ay a m EH Fas Humba J Jaidey Click the Accounting Number Format a e E a j iin GTR button in the Number group AD Qed 512623 i Oo ai Ye ay ay Excel adds a dollar sign aligned Q with the left side of the cell 1 160 502 00 160 502 00 Excel reserves space for a right l p 1 parenthesis for negative values GQ V Click the Decrease Decimal Place button 133539 133559 0 S Each click removes a decimal place 4 lf you click the Increase Decimal
21. e Find and Replace dialog box to find information Use the Replace tab in the Find and Replace dialog box to find and replace information You can use substitutions in the Find and Replace dialog box You can use the asterisk as a substitute for any sequence of characters You can use the question mark as a substitute for any single character For example typing ber finds September October November and December Typing J ne finds Jane and June A s worksheets get larger finding the information Find and Replace When you click the Find All button Excel by default finds every instance of the value you are looking for in the active worksheet and lists the workbook worksheet cell name cell address value and formula for each found value at the bottom of the Find and Replace dialog box When you click Find Next Excel moves to the first instance of the value and Excel moves to the next instance with every additional click of the Find Next button If you want to replace the values you find with a new value click Replace All on the Replace tab to replace every instance of the value Click Replace to replace the selected instance of the values and then move to the next instance Click Find Next if you want to move to the next instance without replacing the selected instance In the Find and Replace dialog box you can use the Options button to set additional options FIND e l a a ja pee ge een E
22. e Number Alignment Font Border Fill or Protection you want to find and or replace Use the Formatting button to restrict your search to characters formatted in a certain way such as bold or percentages Before you start a new Find and or Replace for formats make sure you clear all formats by clicking the down arrow next to the two Format buttons and then clicking Clear Find Format and Clear Replace Format Find and Replace Formats Click the Home tab Click Find amp Select in the Editing group Heer Formulas ra Font F Click Replace Alternatively you can press Ctrl H to open the Find and Replace dialog box Sales Adams Henry 5 Delay Harry Fish Mary 31 197 24 596 8939 40 431 Franklin Florence Goldblatt Sally Harvey Taylor James Serena Jamison John Lasi Sam 17 163 34 703 19 681 O 24 596 In the Find and Replace dialog al 33 E 9 419 5 35 360 5 40 681 Find and Replace xlsx Microsoft Excel Reviews View Add Ins General E Conditional Formatting S GA Format as Table E Cell Styles Data F a Alignment Go To Special Fermas Total Comments 85 460 105 089 36 340 70 224 34 909 107 144 Ti Selection Pane 21 781 97 752 24 830 66 361 35 453 Sales Sales Conditional Formatting 28 982 Constants 0 288 11 521 31 804 21 315 24 368 41 010 12 280 Data Validation Select Objects
23. e Replace All button to replace all instances This example uses Replace All January gt 100 345 169 122 166 274 146 132 581 873 Click Find and then Replace to find and replace the first instance then click Find Next to find the next instance Excel replaces the data Sales 2009 lt _ February Sales 2008 February 2008 186 386 197 980 108 587 162 663 655 616 186 386 197 980 108 587 162 663 655 616 March 2008 152 862 110 749 122 681 sm Find and Replace 50 Fmd Zagare Find whet 2008 Replace with 2009 lt f March 152 86 G A message box appears telling you Excel made replacements Click OK Replace with 2003 Click the Close button to close the Find and Replace dialog box You can click the Options button on the Find and Replace tabs of the Find and Replace dialog box to set several options In the Within field select Sheet if you want to search only the active worksheet Select Workbook if you want to search the entire workbook In the Search field select By Rows if you want to search right to left across the rows Select Column if you want to search top to bottom down the columns Select the check box in the Match Case field _ changes to i if you want your match to be case sensitive For example if this option is not selected abc is considered the same as ABC or aBc Select the check box in the Match Entire Ce
24. ed as text If you line to make the cell wider want to format a number as text as you type it precede the number with an apostrophe continued gt Format Numbers continued en using the Format Cells dialog box you W use the Number format option to apply special formats to your numbers You can set the number of decimal places specify whether your number should display a thousands separator and determine how to display negative numbers You can choose from four formats for negative numbers preceded by a negative sign in red in parentheses or in red and parentheses The Currency format offers you the same options as the Number format except you can choose to display a currency symbol The currency symbol you choose determines the options you have for displaying negative numbers If you choose the dollar sign thousands are separated by commas by default Format Numbers continued Excel designed the Accounting format to comply with accounting standards When using the Accounting format if you use the dollar sign symbol the dollar sign aligns with the left side of the cell decimal points are aligned a dash displays instead of a zero and negative values display in parentheses Countries vary in the way they display dates and times Use the Date and Time format option to choose a locale If you choose English U S you have more than 15 ways to display a date and a variety of ways
25. ey Click the down arrow next to Insert in the Cells group 3 3 Chapman 4 4 Davis A menu appears E Gleranklin Click Insert Cells 6 6 George 7 7 The Insert dialog box appears rs Click the direction in which you want to shift cells A B changes to Wao 1 Adams Click OK ee ea 3 Chapman 5 O shea Excel shifts the number of 4 4 Davis aire cells you selected rs a z C Ente aunn Note If you want to delete cells 6 6 Franklin select the cells click Home 7 7 George click the down arrow next 8 to Delete click Delete Cells choose the direction in which you want to shift the cells and then click OK INSERT COLUMNS OR ROWS rc dd a F Insert Columns or Rows xlsx Microsoft Excel Click and drag column or B e T gt laa 4 Conditional Formatting 3 Insert row labels where you want nigel aga SPS aZ E E o E Formatas Tables 3 Delete 7 to insert columns or rows sia eae ake Ss E cen styles Clipboard 13 ai Alignment a _Mumber This example uses rows Al G f Click the Home tab Se ee ee een one ane gt January February March E Inge in the Cells Region1 100 345 186 386 152 862 i Region 2 169 122 197 980 110 749 Region 3 166 274 108 58 122 681 146 132 162 663 114 330 3 4 gt Region 4 6 T Excel inserts the columns or rows January February March Region 1 100 345 186 386 152 862 Region 2 169 122
26. gonal cate acd bna sak tiras hel sra epeched for che opershng spstem Panas shat an astensk ae nob offected ky opsig system seing P cipean a Fane a Alisnmeni a Hoir h 2 Excel formats the dates T E F La c E TIMES Os gt 01 12 08 01 25 08 03 04 07 1 O m Click and drag to select the cells you want to format a ma Click the Number group s launcher lt 2 EE T 3 In the Number tab of the Format Cells dialog box click Time FQ l V Click to choose a format type S Click OK Qa Excel formats the time a eooo ome E e E A B C 6 gt 9 30 00 PM 1 00 00 AM __ 6 30 00 PM FRACTIONS 7 0 25 0 37 L7 amp 1 4 37 100 13 4 Click and drag to select the cells you want to format iamai Cells ix Click the Number group s launcher inthe Number tab of the Format Cells dialog box 3 click Fraction Click to choose a format type Click OK Excel formats the numbers as fractions These cells show how Excel formats the numbers Excel has several special formats You can preformat cells You can set the default number you can use to format Social so that when you enter of decimal places Excel applies Security numbers ZIP codes and data into a cell Excel when you type a number into a phone numbers To apply the automatically formats it worksheet Click the Office Social Security number special To preformat the cells button A
27. heck mark on the formula bar or you can press the Enter key to enter your data into a cell ENTER TEXT Move to the cell in which you want to enter text 2 Type the text you want to enter Press Enter Excel enters the text into the cell and then moves down to the next cell Alternatively you can click the check mark on the formula bar to enter data ENTER NUMBERS a Move to the cell in which you want to enter a number 2 Type the number you want to enter Press Enter Excel enters the number into the cell and then moves down to the next cell You can also click the check mark on the formula bar to enter data 2 ENTER NUMBERS AS TEXT o Ct a Move to the cell in which you want to enter a number as text Type an apostrophe followed by the number you want to enter Click the check mark Excel enters the number into the cell B 4569275 G Employee Name Employee Number Hire Date James Love Alternatively press Enter B If you receive an error click the Error button and then click Ignore Error ENTER DATES 1 Move to the cell in which you want to enter a date 2 Type the date you want to enter Click the check mark Excel enters the date into the cell Alternatively press Enter Employee Name Employee Number Hire Date James Love 4569275 05 23 2007 When you enter numbers as
28. ll Contents field _ changes to v type in the Find What field to match the cell contents and not contain any extraneous information For example say one cell contains the value Jane Smith and another cell contains the value Smith If you select Match Entire Cell Contents Excel will find Smith but not Jane Smith if you want what you J Jaidey e e Q z Ga U ct 3 a A Find and Replace Formats ells can contain numbers text formats and formulas With Excel you can search for any of these elements to view them replace them or perform some other action You may for example find and replace values to correct mistakes or perhaps you need to return to a value to add a comment or apply formatting You can access the Excel Find and Replace dialog box on the Home tab in the Editing group or by pressing Ctrl H The Find feature is part of Find and Replace and is available on the Home tab in the Editing group or by pressing Ctrl F To find and replace formats specify what you are seeking and with what you want to replace the item you are seeking Click the Options button in the Find and Replace dialog box to specify additional details Use the Within drop down menu to indicate whether to search the current worksheet or the current workbook In the Find and Replace dialog box clicking a Format button opens the Find Format or Replace Format dialog box You can use these dialog boxes to specify th
29. lternatively click the first cell hold down the Shift key and then click the last cell E SELECT NONCONTIGUOUS m z CELLS 1 Click the corner of the first block of cells Drag the mouse to highlight the desired cells Press Ctrl Select the next block of cells Repeat Steps 3 and 4 to select additional cell blocks Re inl SELECT COLUMNS OR ROWS LP B Click the label for the first column or row you want to select Drag to the last column or row you want to select Excel selects the columns or rows co w D o aw Ja You can format multiple worksheets at the same time For example say you are collecting data for three different regions and want to present your data in three worksheets that use the same format Select three worksheets and type the formatting once to have it appear on all three worksheets You select multiple worksheets by holding down the Ctrl key as you click the tab of each worksheet you want to select When you enter data or make changes to any one of the selected worksheets Excel changes all of the other selected worksheets as well To deselect multiple worksheets click a tab for an inactive worksheet while not holding down the Ctrl key You can also use the arrow keys to select cells Click in any cell hold down either the Shift key or the F8 key and then use the left right up and down arrow keys to expand your selection To select noncontiguo
30. menu appears Click format type nine digits into a cell select the cells you want to Excel Options in the lower right Click in the cell Click the launcher preformat and then apply corner Click Advanced Make in the Number group The Format the format to them When sure the Automatically Insert Cells dialog box opens to the you type data into the Decimal Point check box is Number tab Click Special in the preformatted cells Excel checked Type the number of Category box Click Social Security automatically applies the decimal places you want in the number in the Type box Click OK format to your data Places field Click OK Excel formats the digits you entered as a Social Security number Format Cells ormatting enhances the presentation of reports 2 Rows and column headings give your data a visual orientation and highlight important information about the structure and content of your data You can use the Home tab to format cells in a variety of ways Clicking the launcher in the Font or Alignment group opens the Format Cells dialog box here you can format numbers align data within or across cells apply a variety of formats to fonts add borders and fill cells with color Many of the options available to you in the Format Cells dialog box are also available in the Ribbon When you use the Ribbon you can frequently apply a format with a single click You can set off cells by applying a colored background and changing
31. ring data Microsoft Excel is an electronic worksheet You can use it to enter display manipulate analyze and print the information you organize into rows and columns Each Excel 2007 worksheet has more than 1 million rows and more than 16 000 columns Excel labels each row in numerical order starting with 1 Excel labels each column in alphabetical order starting with A When Excel reaches the letter Z it begins ordering with AA AB AC and so on You refer to the intersection of a row and column as a cell The intersection of a cell also forms the cell name For example you refer to the first row in column A as cell A1 and the seventh row in column C as cell C7 When using Excel you enter your data into worksheet cells Enter Data To move to a cell move your mouse pointer to the cell and then click The cell becomes the active cell and Excel surrounds it with a black border Once in a cell you can use the arrow keys on your keyboard to move up down left and right You can enter text numbers dates and formulas into cells Alphabetic characters and numerical data you do not use in mathematical calculations are text Any sequence of characters that contains a letter Excel considers text By default Excel considers all numerical data numbers If you wish to enter numerical data as text precede your entry with an apostrophe As you type the data you enter into a cell appears on the formula bar You can press the c
32. several buttons you can use to format numbers quickly Click the down arrow next to the Accounting Number Format button to choose to apply a United States currency format a United Kingdom currency format a Euro format or another currency format Use the Percent Style button to display the value in a cell as a percent Use the Comma Style z ormatting makes your data easier to read and Format Numbers button to display the value in a cell with a thousands separator Use the Increase Decimal and Decrease Decimal buttons to increase and decrease the number of decimal places The Number Format box is located on the Home tab in the Number group You can use it to format numbers quickly Just click the down arrow to display a menu of options Then click a format option to apply it to a cell or cell range Click the More Number Formats option to open the Format Cells dialog box You can also click the launcher in the Number group to open the Format Cells dialog box The Format Cells dialog box has four categories you can use to format numbers General Number Currency and Accounting The General format is the default format It displays numbers exactly the way you type them CEHE js ome Insert AOL GENERAL NUMBER FORMAT Page Layout Format Numbers Dates and Times xlsx Microsoft Excel Formulas Data Reviews View Adel Ine Type numbers la hs cate a Sl 2 General JE Conditjgagl Formatting 3 Ins
33. ss toolbar TABS Click a tab to view Ribbon options RIBBON Click the buttons in the Ribbon to execute Excel commands B CELL Enter data into cells SHEETS Each workbook has multiple sheets You can enter data into each sheet hen you open an Excel workbook Excel presents the Excel window Your window should be similar to the one in the illustration It may not be quite the same because 0 Booki Microsoft Excel INSERT SHEET Click to insert a new sheet zoom Drag to adjust the magnification of your worksheet STATUS BAR Right click to adjust what appears on the status bar RECORD MACRO BUTTON Click to begin recording a macro SELECT ALL BUTTON Click to select everything in your worksheet NAME BOX Displays the name of the active cell Excel ae iA based on the size S your screen the resolution to which your screen is set and the other screen display options I a e e paves SUN DROP DOWN LIST Displays a list of defined range names FORMULA BAR Use the formula bar to enter and edit data INSERT FUNCTION Opens the Insert Function dialog box ENTER BUTTON Click to accept a cell entry CANCEL BUTTON Click to cancel a cell entry EXPAND FORMULA BAR Click to make the formula bar larger orksheets divide information into rows and W columns of data People often use worksheets to calculate financial statistical or enginee
34. st common way to select cells is to click and drag Excel highlights the selected cells The range of cells you select does not have to be contiguous You can hold down the Ctrl key as you click and drag to select noncontiguous groups of cells If you do not hold down the Ctrl key Excel deselects the first range of cells when you begin to select a new range of cells If you select multiple ranges of cells Excel highlights each selected range Select Data You can select a single cell or the entire worksheet To select a single cell click in the cell To select every cell in a worksheet click the Select All button or press Ctrl A To select an entire row or an entire column simply click the row or column identifier For example to select all of the cells in column C click the C identifier for the column To select multiple columns click the first column and then continue holding down the mouse button as you drag to the other columns you want to select To select entire rows you click the row identifiers on the left side of the rows You can quickly select a large range of cells by clicking in the first cell you want to select holding down the Shift key and then clicking in the last cell you want to select SELECT THE ENTIRE WORKSHEET Click the Select All button Alternatively press Ctrl A SELECT CONTIGUOUS CELLS Click the first cell you want to select Drag to the last cell you want to select A
35. text an Error button may appear Excel is checking to see if you entered the number as text by mistake You should click the button and then click Ignore Error When you press Enter after typing an entry into a cell by default Excel moves down one cell If you want Excel to move to the cell to the right press the right arrow key or the Tab key If you want Excel to move up press the up arrow key If you want Excel to move to the left press Shift Tab or the left arrow key By default when you press the Enter key after typing an entry Excel moves down one cell You can change the default location to which Excel moves Click the Office button A menu appears Click Excel Options in the lower right corner The Excel Options dialog box appears Click Advanced Make sure the After Pressing Enter check box is selected and then choose Right Up or Left in the Direction field to cause Excel to move right up or left when you press Enter J Jaidey e e Q z Ga U ct 3 a A Format Numbers helps you conform to company country or industry standards for formatting Excel provides a variety of options for formatting numbers dates and times By applying formatting you change the way a number date or time appears For example you can use Excel s formatting options to tell Excel you want to separate the month day and year of a date with slashes The Number group on the Home tab has
36. the font color Use a fill to create a colored background for a cell You can set off columns or other important information by applying borders A border adds color to the lines that surround a cell You can choose the type and thickness of the border line and you can choose to apply your border only to the sides of the cell you specify When applying a border you can choose the color style and placement of the border On the Home tab in the Font group Excel provides an Increase Font Size button and a Decrease Font Size button You can click the Increase Font Size button to make your font larger You can click the Decrease Font Size button to make your font smaller You can also enter a font size directly into the Ribbon and or select a new font TEEDE J Apply Formats xlsx Microsoft Excel CREATE A FILL Insert Page Lepoul Formulas Dala Review Tiew Add lrg f alibri l Sl F General r E Conditional Formatting 7 Insert aD Click the Home tab Ca A i B ge Format as Table SY Delete F E N a e zz g ee EP Cal Stes 7 E Format Click and drag to select the cells you eee ee 3 she call want to format i A oan Schedule mn j Click here and then select a fill color E LLL Siarmdard Cors 2 an BEEBE __ mori digas Se Cor End Totalan Repaymentan Balance E 1 515 186 1 S22 517 008 5 5 000 5 2 00 a 2 12 008 1 441 13 449 5 000 8 449
37. to display time To learn more about dates and times see Chapter 3 The Percentage option converts numbers to a percent You can choose the number of decimal places you want to display The Fraction option converts numbers to a fraction If your locale is English U S you can use the Special format option to format ZIP codes phone numbers and Social Security numbers PERCENTS Click and drag to select the numbers you want to format 2 Click the Percent Style button in the Number group Excel converts the numbers to percentages DATES Click and drag to select the cells you want to format Click the Number group s launcher In the Number tab of the Format Cells dialog box click Date Click to choose a format 2 6 6 Q Click OK Format Numbers Dates and Times xlsx Gp asao 7 Home Insert Page Layout Formulas Cats Review Wier Add Ins 4 A Taliti Js fae Ei s 2 Paste 7 z u e gt A ljea EE Clipboard m Fort m Alignment Number A G f A B C 4 0 35 1 1 25 L 5 39459 January 25 2008 4 Mar 07 6 0 895333333 1 00 AM 18 30 rae 0 25 0 37 1 75 3 10 Clipboard Frl P Aligrimertl ia Murnber a 2 AS lt lt t 39459 100 January 25 2008 4 Mar 0 L HAEA 18 30 39459 Locala kanat i Ergleh iLa Dala irmals depla dale and line sada rombas a3 dala rales Dabe formats Dhal bajn sth an astenst Frespond to changes nre
38. us ranges of cells select the first range of cells Click Shift F8 and then select the next range of cells You can press Ctrl Shift an arrow key to select everything from the active cell to the next blank cell that is to the right to the left above or below the active cell J Jaidey e e Q z Ga U ct 3 a A Copy Cut and Paste Cells l f you want to use the same values in multiple For example you can copy a list of data in one worksheet to another worksheet or you can copy a formula to multiple other cells When you copy and paste a cell or range of cells Excel duplicates everything in the cell including the cell values formulas formatting comments and data validation and leaves the original cell values unchanged You can select copy and paste multiple cells only if the cells are adjacent If you want to move information from one location to another you can select cut and paste Cutting and pasting removes data from the original location and locations you can copy and paste instead of retyping places it in a new location When you apply the Cut or Copy command to a range of cells Excel surrounds the cells with a dotted line The selected cells remain marked until you paste or press the Esc key to deselect the cells After you cut or copy a range of cells you can paste the cell contents to any location within your current workbook another Excel workbook or
39. you delete cells rows and columns the same is true however when you delete a cell that you Insert and Delete Cells directly reference in a formula Excel cannot adjust the formula and displays a REF error instead If you want to insert columns select the number of columns to the left of where you want the new columns and then select the Insert Column option For example if you want to insert three columns select three columns and then select the Insert Column option If you want to insert rows select the number of rows above where you want the new rows and then select the Insert Row option For example if you want to insert three rows select three rows and then select the Insert Row option If you want to insert nonadjacent columns or rows hold down the Ctrl key as you select where you want to place the rows or columns BI Z k Microsoft Excel INSERT CELLS amp ome 2 Formulas Dats Review View Add Ins i i 3 ai fee ie gt Gig Conditional Formatting Inserts 3 Click the point at which you a Sees saem ana a i 4 want to insert cells me g Dene Sam Be e ad ag cou sete PT et sheet pre f Clipboard Ta e ia Abgnmerit f Number Ta Styles Ge Tisert Sheet Columns E Select multiple cells if you B5 0 4 Franklin LAD inser Sheet want to insert multiple cells p A T C D z F Click the Home tab SEN 1 Adams l 2 2 Brown
Download Pdf Manuals
Related Search