Computer application in business

What is Microsoft Excel What is a spreadsheet How to substantiate the ingredients and user interface of Microsoft Excel How to substantiate and intellect the states of trifles and machinebars Types of cell entries How to provide Excel, unconcealed, husband and vocableinate Excel smooths 1 1.1 Introduction to Microsoft Excel 2003 Welcome to Microsoft Excel 2003! This is the preceptor manual that is built and to be used by the preceptor who accomplish be counsel BCOMP1101 ? Computer Impression for Business to Business Year 1 Semester 1 tyros. In this preceptor manual, tshort accomplish be exstanding encircling Microsoft Excel 2003. It is recommended you should use Microsoft Excel 2003 impression to indoctrinate the tyros past this software is calm?} amplely used at the avail resisting that Microsoft Office 2007 accomplish be released in 2007. Currently the statement of Microsoft Excel 2003 is Excel 11.0 and alikeize (forthadvenient a end unundeniable patches to upprogression the impression). You calm?} can use some of the older statements of Microsoft Excel such as 2000 and 2002 (Office XP statement) but some signs may not be adapted nor are incongruous paralleld to Microsoft Excel 2003. 1.2 What is a Spreadsheet? Microsoft Excel is a potent spreadshuffle program confesss you to constitute axioms, exhaustive estimations, mould resolutions, graph axioms, enucleate administrative seeming relations, publish constituted axioms to the Web, and approximation to genuine-season axioms from Web sites. The lewd elder power of Excel are: 1. Worksheets Worksheets confess you to penetrate, proportion, treat, and excite axioms such as gum and citation. The vocable is-sueshuffle media the tallying as spreadsheets. 2. Charts Charts pictorially state axioms. Excel can induce a diversity of twodimensional and three-dimensional charts. 3. Databases Databases train axioms. For in, unintermittently you penetobjurgate axioms onto a is-suesheet, Excel can schemeation the axioms, exploration for local axioms, and excellent axioms that coalesces criteria. 4. Web Support Web deeptenance confesss Excel to husband is-suebooks or power of a workbulk in HTML fashionat so they can be aimed and treatd using a browser. You so can approximation genuine-season axioms using Web queries. A spreadshuffle is a despite consultation (or grid) of recognizeledge, frequently financial recognizeledge. The enjoin came from "spread" in its notion of a newstract or abundance ace (citation and/or picturesques) that covers two oppositeness pages, extconposteriority despite the cpenetobjurgate infold and treating the two pages as one vast one. The unification enjoin "spreadsheet" came to mediocre the fashionat used to exhibit bulkregard ledgers—forthadvenient a end supports for categories of expenditures despite the top, invoices registered down the left room, and the quantity of each blot-outment in the cell wshort its row and supmien intersect— which were traditionally a "spread" despite oppositeness pages of a skip ledger (bulk for regard resemblanceing registers) or on alikeizesized shuffles of tract resolute into rows and supports in that fashionat and obstructly twice as ample as beggarlysettle tract. A is-suebulk is a abundance of is-suesheets. A is-sueshuffle is a grid of supports and rows in which you penetobjurgate labels, compute and axioms. Cells are constituted into rows and supports to geneobjurgate a is-suesheet. Each cell can redeep up to 32,000 missives. Tshort are 256 supports run uprightly up and down a is-sueshuffle seeing tshort are 65536 rows run insipidly from left to straight despite a is-suesheet. 2 1.3 Microsoft Excel 2003 User Interface The user interface of Microsoft Excel has some alikeities thriveing a end Microsoft Enjoin exclude for undeniable menus, machinebars and bids. On this page, a exemplification curtainshot of Microsoft Excel 2003 is professionn short thriveing a end labels to state the user interface ingredients. Figure 1.1: Microsoft Excel 2003 interface The Demusic Bar is establishd at the top of the window apprehends the impression Controlmenu icon, the impression state, the smoothstate of the locomotive instrument, and the Minimize, Renew (or Maximize), and Terminate trifles. The smoothstate of the locomotive instrument is so unconcealed as the is-suebulk state. A is-suebulk is a abundance of is-suesheets. A is-sueshuffle is a grid of supports and rows in which you penetobjurgate labels, compute and axioms. Just adown the demusic bar is the menu bar. The menu bar sojourns the menus for the impression such as File, Edit, Aim Insert, Format, Tools, Data, Window and Help. On the tallying row of the menu bar, tshort are Ask a Interrogation arena box and minimize, renew (or maximize) and vocableinate trifles for that sign locomotive is-suebook. Adown the menu bar, tshort are 2 machinebars, which are banner machinebar and fashionatting machinebar. The machinebars redeep trifles which engagementinate local offices. Equalize of these accomplish be forcible in 1.6 Identifying Toolbars and Buttons. 3 Just adown the fashionatting machinebar are two very twinklingous ingredients of Excel that you accomplish nonproduction them all the season. On the left is the State Box. It vaults the popular locomotive cell or cells. Natant the State Box sojourns the cell relation. A cell relation the supmien epistle and row sum that identifies a cell; e.g. cell A1 connects to the cell at the interindividuality of supmien A and row 1. The Formula Bar is establishd to the straight of the State box. It is used to geneobjurgate and edit computes. The Formula Bar beseems locomotive whenever you provide keying axioms into a cell. When the Formula Bar is locomotive, the Enter, Cancel, and Edit Formula trifles defense. When you excellent a cell, you accomplish mention that tshort accomplish be one supmien phraseology epistle and one row phraseology sum nature highlighted in porconsultation bluish. This is to state that you feel excellented a cell and it became an locomotive cell. At the tallying season, you accomplish see that there’s a dense ebon brink surrounds the locomotive cell. At the proelevate of this dense ebon brink is a inexpressive ebon public box which we fawn it replenish wield. We accomplish see what the experience of the replenish wield is, posterior. However, if you excellent alikeize than one cells, you excellented a stroll of cells. These excellented cells feel their supmien and row phraseologys highlighted in porconsultation bluish to state they are nature excellented. However, tshort can barely be one locomotive cell at a season. In this plight, the locomotive cell is a colornear box seeing the tranquillity of inlocomotive cell are in porconsultation bluish colour. This “colornear box” accomplish be stated as locomotive cell on the state box. The scroll boxes and scroll bars on the straight and proelevate of a is-sueshuffle confesss you to straggle through the completion locomotive is-sueshuffle in enjoin to see the other power of it. At the proelevate of the window, tshort are tab scrolling trifles that you can scroll through the shuffle tabs of the Excel is-suebook. The trifles thriveing a endout the | barely scroll left and straight by a bit seeing the trifles thriveing a end the | accomplish skip to the peculiar and the definite shuffle tabs respectively. Clicking on these tabs scrolling trifles does not fluctuate the locomotive is-sueshuffle aim to another is-suesheet. The tab rive box is mediocret to prescribe the tediousness of the scroll bars. In enjoin to aim other is-suesheets, you feel to click on the shuffle tabs. An locomotive shuffle tab has its enjoins in lion-hearted. You can set colours for shuffle tabs to mould it easier to incongruousiate and substantiate. Excel 2002 and posterior statements feel adowntaking pane signs. Undertaking Pane is a panel to the straight of the window that has numerous offices and signs that you can confer-upsingly approximation and straggle encircling. At the proelevate of the window, tshort is a row that is unconcealed as the standing bar. The standing bar professions some concomitant recognizelaspect encircling the popular locomotive is-suebulk and impression. For in, it may profession keen estimations of excellented stroll of cells retaining gum, num lock, caps lock and scroll lock keys empowerd or not characterized. 1.4 Starting Microsoft Excel 2003 Now, we accomplish comby how to provide using Microsoft Excel 2003 thriveing compassing and perception the basic ingredients and interface of Microsoft Excel 2003. To propel Microsoft Excel 2003, tshort are few ways to do it. 1) Click on the shortcut icon on the desktop, if the shortcut icon is exhibit. 2) Propel the impression from Start Microsoft Excel 2003. All Programs Microsoft Office 4 3) Propel the impression from Start All Programs New Microsoft Office Document. Then pick-out new Excel Workbulk liberty and click Ok. 4) Unconcealed My Computer. Then on the address bar, fashion in/straggle to C:Program FilesMicrosoft OfficeOffice10 and then exploration for EXCEL.exe impression smooth. Note: This mode may be incongruous consistently it insists on how Microsoft Office was naturalized. Once you feel propeled Microsoft Excel, you profession a utter is-suebulk thriveing a end adowntaking pane vaulted as professionn in Figure 1.2. Figure 1.2: Utter is-suebook 1.5 Types of Cell Entries After the impression is propeled thriveing a end a utter is-suebook, tshort are few things you nonproduction to recognize encircling penetrateing axioms into cells in a spreadsheet. 1) Trustworthy ? An minute that does not fluctuate. For in, numeric compute or illustrative citation. 2) Office ? A predefined computational adowntaking. 3) Formula ? A league of numeric trustworthys, cell relations, arithmetic operators and offices. It frequently provides thriveing a end an tallyent expression ( = ). It is twinklingous to imply the 3 fashions of cell entries consistently when you thrive counsels to do an Excel project, you must imply the jargons in the sentences. 5 1.6 Identifying Toolbars and Buttons Right now, thriveing a end Microsoft Excel propeled, it is season to substantiate the machinebars and their trifles. By connectring end to Figure 1.1, we can see three machinebars are brought to circumspection by the arrows and states. The banner machinebar as its state applies, it consists of trifles that tallys to most basic bids in Excel (and so other Microsoft impressions as polite-mannered-mannered, exclude for some undignified estrangements). The fashionatting machinebar is establishd adapted adown the banner machinebar. The fashionatting machinebar consists of trifles that tally to beggarly fashionatting productions. Formatting media to fluctuate the seems and phraseologys of cells. The machinebar at the proelevate of the window is the induceing machinebar. Sketch machinebar consists of trifles that tally to beggarly induceing bids in Excel. Tshort are numerous other machinebars adapted in Excel such as Charts, Forms, Formula Auditing, PivotTable, Protection, Visual Basic, Web and etc. Some of the other beggarly machinebars that we may use are Charts, Forms and PivotTable. Each machinebar has trifles thriveing a end a local sketch/icon to be choice from others. Each of these trifles engagementinate local adowntaking. The thriveing consultation yields you the undesigning recognizelaspect of each adowntaking state, icon/button, how to approximation it from the menu bar or the choice keyboard shortcut. Task Icon / Button Menu Bar Keyboard Shortcut New Blank Document File | New CTRL+N Open File | Open CTRL+O Save File | Save CTRL+S Print File | Print CTRL+P Print Preview File | Sculpture Preview Spelling Tools | Spelling F7 Cut Edit | Cut CTRL+X Copy Edit | Copy CTRL+C Paste Edit | Paste CTRL+V Undo Edit | Undo CTRL+Z Redo Edit | Redo CTRL+Y Insert Hyperlink Insert | Hyperlink CTRL+K E-mail Format Painter 6 AutoSum Insert | Function ALT+= Paste Function Insert | Function SHIFT+F3 Sort Ascending Data | Sort ALT + D | S Sort Descending Data | Sort ALT + D | S Chart Wizard Insert | Chart F11 Drawing View |Toolbars | Drawing Zoom View | Zoom Microsoft Office Excel Help Help | Microsoft Office Excel Help F1 PivotTable and PivotChart Report Data | PivotTable and PivotChart Report ALT + D | P New Comment Insert | Comment ALT + I | M AutoFilter Data | Percolate | AutoFilter ALT + D | F | F Find Edit | Find CTRL+F Find and Replace Go To Font Edit | Replace Edit | Go To Format | Font | Font Tab CTRL+H CTRL+G CTRL+D Font Size Format | Font | Font Tab CTRL+D Bold Format | Font | Font Tab CTRL+B Italic Format | Font | Font Tab CTRL+I Underline Format | Font | Font Tab CTRL+U Align Left Format | Paragraph | Indents and Spacing Tab Format | Paragraph | Indents and Spacing Tab Format | Paragraph | Indents and Spacing Tab CTRL+L Center Align Right Merge and Center Currency Style Format | Cells | Sum Tab Percentage Style Format | Cells | Sum Tab Comma Style Format | Cells | Sum Tab Increase Decimal Format | Cells | Sum Tab Decrease Decimal Decrease Indent Format | Cells | Sum Tab Increase Indent Format | Cells | Alignment Tab Format | Cells | Alignment Tab CTRL+E CTRL+R CTRL + 1 | Number Tab CTRL + 1 | Number Tab CTRL + 1 | Number Tab CTRL + 1 | Number Tab CTRL + 1 | Number Tab CTRL + 1 | Alignment Tab CTRL + 1 | Alignment Tab 7 Insert Borders Fill Color Format | Bsignal and Shading | Bsignal Tab Format | Cells | Precedent Tab Font Color Format | Font | Font Tab AutoFormat Format | AutoFormat CTRL + 1 | Benjoin Tab CTRL + 1 | Pattern Tab CTRL + 1 | Font Tab ALT + O | A Format Cells Format | Cells ALT + O | E Increase Font Size Decrease Font Size Superscript Subscript Language Format | Cells | Font Tab CTRL + 1 | F Format | Cells | Font Tab CTRL + 1 | F Format | Font | Font Tab Format | Font | Font Tab Tools | Diction | Set Language CTRL + 1 | F CTRL + 1 | F Insert WordArt Insert | Sketch | WordArt ALT + I | P | W Insert Clip Art Insert | Sketch | Clip Art ALT + I | P | C Insert Picture From File Line Color Insert | Sketch | From File ALT + I | P | F Tools | Formula Auditing | Formula Auditing Mode File | Exit CTRL + ` Right to Left Sheet Right-toLeft Insert Citation Box Line Style Dash Style Arrow Style Shadow 3-D Formula Auditing Mode Quit Word ALT+F4 Table 1.1: Buttons, menu bids and shortcut keys 1.7 Identifying Menus and Commands Microsoft Excel menus are alikeize or else alove to other Microsoft Office impressions exclude for some menus such as Axioms menu. Each menu has a register of bids. Each bid has a caption epistle which has an adowncord adown it. This confesss user to confer-upsingly run/approximation to that bid by urgent-compulsory the alphabet on the keyboard. Some bids adown the menus feel shortcut keys. Shortcut keys are bids that can be challenged by urgent-compulsory the rectify keys from the keyboard. They can husband season rather then using mouse to straggle the cursor to approximation the menu and bids. Undeniable menus feel submenus, which dilates and vaults alikeize bids that are bunched coincidently and they befit to that submenu bid. 8 1.8 Hole Excel Files After we feel attested and adownstood some of the most twinklingous and commbarely used menus, bids, machinebars and trifles, we affect on to some of the basic adowntakings that users do. Peculiar of all, we seem at unconcealeding Excel smooths. To unconcealed an Excel smooth, you can do it by using one of the modes that you feel compassed from Consultation 1.1. Unintermittently you feel run the unconcealed bid, you should see a dialog box such as the one professionn in Figure 1.3. Figure 1.3: Unconcealed Excel smooth dialog box This Unconcealed Excel smooth dialog box accomplish be one of the most beggarly dialog box that you accomplish see whole season when you use Excel. Tshort are few twinklingous power that you nonproduction to recognize. The Seem In register box confesss you to ooze the register and it professions you the directory of the computer and other drivers. The Seem In register is an twinklingous ingredient that confesss you to confer-upsingly straggle through the infolders in your obduobjurgate disk. On the tallying row, the trifles to the straight of the Seem In register are Back, Up One Level, Exploration the Web, Delete, Geneobjurgate New Folder, Views and Tools. Each of these engagementinates a adowntaking. For in, the Up One Plane trifle straggles end up one projecte of the infolder that you are popularly aiming. You can see some Excel smooths at Figure 1.3 which is aimed as Details. These Excel smooths feel a smoothname, bigness, fashion and spell mitigated. They can be schemeationed in ascconposteriority and descconposteriority enjoins. The most twinklingous sunderneath of this dialog box is in-property the Unconcealed trifle. This trifle has a ooze down arrow that vaults alikeize ways of unconcealeding a smooth. The Files of fashion register can confer-upsingly percolates and vaults the clarified smooth emanationions. To unconcealed a smooth, peculiar you exploration for it, then click on Unconcealed trifle or incork click on it. 9 1.9 Frugal Excel Files Saving a smooth is proportionately twinklingous as to unconcealeding a smooth. Frugal a smooth accomplish abundance the axioms and registers as a computer smooth superveneing a endin the obduobjurgate disk. Tshort are a few ways to husband Excel smooths. 1) Husband bid ? This bid is usually used when you feel already engagementinateed the frugal bid at balanceest unintermittently. When you run Husband bid, it accomplish husband all the axioms into the popular smooth state. 2) Husband As bid ? This bid is usually used when you nonproduction to husband the Excel axioms as another smooth fashion or thriveing a end a incongruous smooth state. Figure 1.4: Husband As dialog box Figure 1.4 professions the Husband As dialog box. It seems alove to Figure 1.3 exclude the bid is incongruous. On top of that, when frugal an Excel smooth, you can state which fashion of smooth you nonproduction to husband as. Each fashion of smooth emanationion has its experience. The most commbarely used fashions are Microsoft Excel Workbulk *.xls and Template *.xlt. We accomplish seem at template in the posterior articles. 1.10 Closing Excel Files Once you high using Microsoft Excel smooths, you may nonproduction to vocableinate it. To vocableinate an Excel smooth, you can click on the ‘X’ trifle of the locomotive is-suebulk (not the impression vocableinate trifle which is establishd on the top-most straight is-sueman recess). When you vocableinate a is-suebulk that has not been husbandd, you accomplish be quick whether you nonproduction to husband the fluctuates made to the popular is-suebulk (file). 10 Summary Microsoft Excel 2003 is popularly the hindmost statement in the traffic (2006). Excel is a spreadshuffle impression that consists of rows and supports to fashion cells. Numerous cells establish up a is-suesheet. By combining numerous is-suesheets, you fashion a is-suebook. Tshort are a few ways to propel Microsoft Excel, unconcealed, husband and vocableinate Excel smooths. Menus redeep registers of bids that run local functions. Toolbars are a bar unmeasured of commbarely used trifles in which each trifle has a local office. Shortcut keys are another way to confer-upsingly approximation and run offices of Excel. If one can substantiate Microsoft Excel user interface, one can amply conquer the basic of Excel in no season consistently the user interface of Excel can aid users to confer-upsingly imply how to use the impression to do a job. 11 Chapter 2 Developing a Administrative Looking Worksheet 2.1 Geneobjurgate Workbulk and Multiple Worksheets 2.2 Penetobjurgate Citation and Numbers 2.3 Use Undesigning Functions (Sum) and Formulas 2.4 Undesigning Formatting 2.5 Page Setup and Orientation 2.6 Printing Workbook OBJECTIVES In this article, tyro accomplish compass: ? ? ? ? ? ? To geneobjurgate utter is-suebulk and multiple is-suesheets To penetobjurgate trustworthy citation and gum To use undesigning offices and fashionulas To devote undesigning fashionatting on is-suesheets To erect is-suebulk for sculptureing Printing is-suebook 2.1 Geneobjurgate Workbulk and Multiple Worksheets First of all, you ask tyros to geneobjurgate a utter is-suebulk by propeling Microsoft Excel 2003. Unintermittently Microsoft Excel 2003 is propeled, you should see the curtain as professionn in Figure 2.1. 12 Figure 2.1: Utter Workbook This utter is-suebulk has 3 is-suesheets which are labelled as Sheet1, Sheet2 and Sheet3. If you nonproduction to instil alikeize is-suesheets into the is-suebook, you thrive one of the two treads adown: 1) Straight click on Sheet1 and then pick-out Instil bid. The Instil dialog box defenses. You should pick-out Workshuffle adown the Unconcealed tab. 2) Click on Instil menu and pick-out Workshuffle bid. To rearstroll the new is-suesheet, click on the new is-sueshuffle tab state and then pull it to the new colonization that you nonproduction it to be. But if the is-suebulk has too numerous is-suesheets, on top of that some of them are not conspicuous (nonproduction to scroll to aim the shuffle tabs), the easier way to rearstroll the is-sueshuffle is by straight click on the shuffle tab that you nonproduction to affect. Then you pick-out Affect or Copy… bid. You excellent a new colonization that you nonproduction to affect the is-sueshuffle to. We can set colours on the shuffle tabs and tranquillityate the shuffle tabs. To delete a is-sueshuffle is undesigning by adapted straight click on the hanker-for is-sueshuffle and then pick-out delete bid. 2.2 Penetobjurgate Citation and Numbers Once you feel adownstood the 3 fashions of cell entries from Article 1, we accomplish provide typing some trustworthy axioms into Shuffle 1 by thriveing the counsels adown. 1) Fashion in Makanlah! @ Limkokwing, Cyberjaya in cell A1. 2) Fashion in January 2006 Sales in cell A2. 13 3) Fashion in the thriveing provideing on cell A3. Stall Malay Chinese Indian Western Beverage Total Week1 5673 1235 5535 4356 7864 Week2 4324 4215 5643 4256 5889 Week3 3672 6341 4564 6765 6876 Week4 3573 4567 5654 3244 7854 Total The estimation for the completion supmien and row accomplish be executed in the direct favoringity. As you fashion the trustworthy citation and gum into each cell, tshort are seasons that you may made succeeds. If you mould a succeed, you can incork click on the cell thriveing a end succeed and then rectify the succeed. An choice way to edit a succeed is by excellenting the cell thriveing a end succeed and then click on the fashionula bar to rectify the succeed. You must feel mentiond that the fashionula bar sojourns the citation/gum that you fashion in. The fashionula bar is an twinklingous sunderneath of Excel that you can see the deviation of the cells. You should feel celebrity that seems love in Figure 2.2. Figure 2.2: Citation and sum trustworthys 2.3 Use Undesigning Functions (Sum) and Formulas Refawn end Article 1, fashionula provides thriveing a end an tallyent expression seeing a office is a computational adowntaking. The direct tcomprehend direct is to use a office to add up all the computes of a support/row and settle the completion computes on the Completion supmien and row. The office that adds up all the computes in the cells is the Sum office. Thrive the treads adown to proelement the completions for each classify. 1) At cell F4, fashion in =sum(A4:E4). Lean Enter. 2) At cell A9, fashion in =sum(A4:A8). Lean Enter. Once you feel executed that, you accomplish see the completion compute in those 2 cells. When you excellent cell F4, you can see the Sum fashionula superveneing a endin the fashionula bar. Ok, so far barely 2 14 cells redeep the Sum office. An choice way to do it is to use the AutoSum trifle. It is keener consistently the Sum office has been clarified as the lapse office to proelement a stroll of cells. However, it can be very tiring to iteobjurgate the typing/guarded the tallying office and fashionula onto the neighboring cells. To confer-upsingly sum up the computes for the other bunchs, the fashionulas superveneing a endin the cells can be copied to neighboring cells by using the replenish wield. The replenish wield is a inexpressive ebon public establishd in the inferior-straight recess of the inert brink encircling the locomotive cell. Standing the mouse cursor alikeize the replenish wield and it accomplish veer into a peevish hair. Left click and pull to neighboring cells to resemblance the fashionulas onto other cells. The other cells in the Completion supmien and row redeep the Sum office and fashionula. You accomplish mention that the cell relation for cell F4 is incongruous from cell F5, F6, F7, etc. The cell relation that fluctuates in this mode is unconcealed as not-arbitrary cell relation. The fashionula in cell F9 can be either =sum(F4:F8) or =sum(B9:E9). In the end, twain fashionulas adaptedty the tallying completion compute. Forthadvenient thriveing the treads alikeize, you should get a alove adaptedty as professionn in Figure 2.3. If you mention that there’s a colon ( : ) in natant F4 and F8, it states a stroll of cells whereby F4 is the peculiar cell relation and F8 is the definite cell relation. Figure 2.3: Adventitious fashionulas 2.4 Undesigning Formatting Once you feel exhaustived penetrateing the citation, gum and fashionulas into the is-suesheet, the direct tcomprehend is to improve the seem of this is-sueshuffle ? that is to do fashionatting. Thrive the counsels adown to fashionat the register. 15 1) Excellent cell A1 to F1. Click on Associate and Cpenetobjurgate trifle from the formatting machinebar. Click on Lion-hearted trifle and fluctuate the font bigness to 14. Fluctuate the endground colour to bluish-grey by clicking on Replenish Color. Finally, fluctuate the font colour to colornear colour. 2) Excellent cell A2 to F2. Click on Associate and Cpenetobjurgate trifle from the fashionatting machinebar. Click on Lion-hearted trifle and fluctuate the font bigness to 14. Fluctuate the endground colour to bluish-grey by clicking on Replenish Color. Finally, fluctuate the font colour to red colour. 3) Excellent cell A3 to F3 and lion-hearted the supmien denotes. Then you add a proelevate brink confer-uponly. 4) Excellent cell B3 to F3 and then align straight the denotes. 5) Excellent cell A4 to A9 and lion-hearted the row denotes. Then you add a straight brink and a left brink confer-upsingly for cell A3 to A9. 6) Prescribe the supmien A width to 120 pixels. 7) Excellent cell A4 to A8 and click on Growth Indent trifle unintermittently. 8) Excellent cell A9 to F9 and add a top and incork proelevate brink. 9) Excellent cell F3 to F9 and add a straight brink. 10) Excellent cell B9 to F9 and add a useize publicity fashionat by approximationing Format Cells Number Tab Custom liberty. Fashion in the thriveing onto the Type: arena “RM”0.00 Then you click on Decrease Decimal trifle twice and then align straight the completions. 11) Excellent cell B4 to F8 and then align straight the digits. After you feel exhaustived these 11 treads, you should see a register as professionn in Figure 2.4. Figure 2.4: Formatted consultation 16 2.5 Page Setup and Orientation After you feel high fashionatting the consultation, you conposteriority on to the direct sight which is page setup. Page setup bid is approximationed from Smooth menu. Page setup deals thriveing a end page repairments of the popular locomotive is-sueshuffle or excellented is-suesheets. The Page Setup dialog box has 4 tabs: Page, Margins, Header/Footer and Sheet. The Page tab has orientation, scaling, tract bigness, sculpture nature and page sum repairments. The twinklingous sunderneath of this tab is the sculptureing orientation, scaling and tract bigness. If in any pvoid that your is-sueshuffle is very big in which the sculpture out accomplish dilate to multiple pages thriveing a end scaling set to 100%, you may nonproduction to sculpture wholething on one tract. Thus the Fit to liberty is very beneficial that is if you do not recognize what percentage to use for the Prescribe to repairment. The Margins tab has repairments to prescribe the is-sueshuffle from the aspect of the tract, as polite-mannered-mannered-mannered as the Cpenetobjurgate on page repairments. Bear in intellect, the rooms computes insist on your Excel repairments, which can be in centimeters, inches, etc. The Header/Footer tab sojourns the repairments to apprehend header and footer to the excellented is-suesheet(s). Headers and footers are establishd at the top and proelevate rooms of the sculpture outs. The header and footer in Excel are incongruous from Microsoft Enjoin and PowerPoint whereby in Excel, tshort are use header and footer that feel 3 favoringitys: left, cpenetobjurgate and straight. Past they are useizable, you can add obstructly anything such as season, spell, citation, smooth colonization, page sum, sketchs, etc. The Shuffle tab confesss you to set your sculpture area, rows and supports to iteobjurgate on top and left, and other sculptureing repairments. However, usually the most twinklingous experience of this tab is the sculpture area and iteobjurgate sculptureing denotes alikeize multiple pages. You accomplish comby this shortly in the advenient articles when you are established on vast is-suesheets. Now, you endure your Excel is-sueshuffle by approximationing the Page Setup bid. The repairments that you nonproduction to prescribe are: 12) Page orientation: Landscape 13) Tract bigness: A4 14) Cpenetobjurgate on Page: Horizontally 15) Margins: Top & Bottom 2.5, Left & Right 1.9, Header & Footer 1.3 16) Use header Cpenetobjurgate favoringity: Fashion Article 2: Developing a Professional Looking Worksheet 17) Use footer Left favoringity: Fashion the thriveing in 3 cords: Last updated on & &[Time] 18) Use footer Right favoringity: Instil Page sum 17 2.6 Printing Workbook Once you feel exhaustived the is-suebulk repairments, it is season to sculpture out the is-sueshuffle (past there’s barely one is-sueshuffle thriveing a end the consultation). Frequently intellect to use the Sculpture Preaim bid to curb how your is-sueshuffle accomplish seem love precedently you provide sculptureing bid. If you gard that the layout is not suitably align or there’s celebrity detriment or inaccurate, you can mould the rectifyion on the is-sueshuffle thriveing you use sculpture preaim bid. After you feel already curbed and developed the consultation, recognizeledge, fashionatting and is-sueshuffle repairments are rectify, you can sculpture out the is-suebook. Printing is undesigning and tshort are two ways to do it: Sculpture trifle on Banner machinebar, or lean CTRL+P. Summary Microsoft Excel is-suebulk is a erective spreadshuffle bulk smooth that can apprehend multiple is-suesheets. Each is-sueshuffle sojourns cells that mould up rows and supports. When you provide established on Excel is-suebook, you should project out the resigned for the sum of is-suesheets you accomplish use, axioms to penetrate, layout, scheme, fashionatting, macros, charts and page setup. After projectning, you should provide adding is-suesheets to the is-suebook. The relieve tcomprehend is to fashion in the raw axioms, citation, and gum and then apprehend sketchs (if compulsory). Forthadvenient that, you should add offices and fashionulas to delayredeep cells of the completion is-suebook. The offices and fashionulas may defense any sunderneath of the is-sueshuffle and p despite multiple is-suesheets, if judge compulsory. Moving on, fashionatting tcomprehend comes into the sketch. Formatting is the lewdth sight unintermittently you feel most of the recognizelaspect settled into the is-suesheets. Knowlaspect accomplish be alikeize beneficial and comprehendable thriveing you devote fashionatting. Charts and PivotTable and PivotChart (you accomplish comby PivotTable and PivotChart in the posterior articles) thrives suit; and you may nonproduction to fashionat the charts and consultations concurrently the way. Forthadvenient fashionatting, you accomplish nonproduction to prescribe the page setup repairments of the is-suesheets or is-suebulk (depconposteriority on the is-sue you are established on). Printing comes definite. It is recommended to thrive the treads (mentioned alikeize) as vocableinately as you can. Nevertheless, you are unoccupied to use Excel in any way you nonproduction, as hanker as you can engagementinate the adaptedtys you nonproduction at the end of the day. 18 Chapter 3 Enhancing Workshuffle thriveing a end More Formatting and Adding Cell Reference 3.1 Template 3.1.1 Tyro Season Consultation Template 3.2 Cell Reference 3.2.1 Relative, Arbitrary & Adulterated Cell References 3.2.2 3-D Cell Reference 3.2.3 Consolidating Cell Reference 3.3 Advance Formatting Cells 3.3.1 Subject Formatting 3.4 Navigating In Excel OBJECTIVES In this article, tyro accomplish compass: ? ? ? ? ? To geneobjurgate and use template To imply and use not-absolute, arbitrary & adulterated cell relations To imply and use 3-D cell relation To consolispell spell through cell relation To do alikeize fashionatting on cells 19 3.1 Template In Excel, Template is a is-suebulk that has already nature made thriveing a end preferred fashionatting, undignified resigned but redeep no raw axioms. Template can be reused intermittently and intermittently, as numerous seasons as potential. In numerous plights, template does not fluctuate in deexpression and it can be executed so by locking and compensating cells. You can gard of Excel template, for in love an beggarlysettle void fashion that you replenish up your peculiar specialtys for creating a bank frugal resemblance. Now, we accomplish seem at how to geneobjurgate a beneficial tyro season consultation using Excel and then husband it as a template. 3.1.1 Tyro Season Consultation Template Please thrive the counsels circumspectly to fix that you geneobjurgate an obsequious tyro season consultation template. 19) In cell A1, fashion in . We use the transmould brackets ? < > to state that user can resettle the resigned on this cell. 20) From cell B2 to C2, fashion in 1 and 2 respectively. Then you excellent cell B2 and C2 and then aim to the replenish wield. Click on the replenish wield and pull to cell J2. Figure 3.1: Pull replenish wield 21) From cell B3 to J3, fashion in 9:00 – 10:00, 10:00 – 11:00, 11:00 – 12:00, 12:00 – 13:00, 13:00 – 14:00, 14:00 – 15:00, 15:00 – 16:00, 16:00 – 17:00, 17:00 – 18:00 respectively. Figure 3.2: Duration entries 22) Penetobjurgate Monday and Tuesday onto cell A4 and A5 respectively. Then you use the replenish wield to resemblance the coherent days up to cell A9. Figure 3.3: Days copied thriveing a end replenish wield 20 Once you feel exhaustived these 4 treads of penetrateing citation and sum trustworthys, the relieve tcomprehend is to diversify the defenseance of this season consultation, which is fashionatting. 23) Excellent cell A1 to J1 and then associate and benevolence. Then fluctuate the font to Arial Ebon and fluctuate the bigness to 24. End you are calm?} excellenting the associated cell, approximation Format Cells Benjoin tab. Pick-out the dense cord (straight supmien relieve definite phraseology) and then click on Outcord trifle adown Presets. 24) Excellent cell B2 to J2, align cpenetobjurgate the gum, fluctuate font bigness to 16 and then lion-hearted them, 25) Excellent cell B3 to J3, align cpenetobjurgate the durations and lion-hearted them. 26) Excellent supmien phraseology B to J and then prescribe the supmien width to 110 pixels. Prescribe the supmien phraseology A to width of 120 pixels. 27) Excellent row phraseology 4 to 9 and then prescribe the row summit to 100 pixels. 28) Excellent cell A4 to A9 and then approximation Format Cells dialog box. On the Alignment tab, pick-out Cpenetobjurgate for Insipid and Vertical. Then aim Font tab and fluctuate the font bigness to 11 and lion-hearted the deviation. Figure 3.4: Formatted season consultation 29) Now we accomplish add alikeize brinks to this consultation. Peculiar of all, excellent cell A2 to J9 and then approximation Format Cells dialog box. At Brink tab, use the tallying brink phraseology and then click on Outcord and Natant trifles. 30) Then excellent cell A2 to J3 and then approximation Format Cells dialog box intermittently. At the Brink tab, click on the trifle wshort the arrow is aiming at as professionn in Figure 3.5. Then click Ok. The balanceing is we nonproduction to reaffect the insipid cord that divides the gum 1 to 9 and durations. 21 Figure 3.5: Reaffect insipid cord superveneing a endin 31) Lastly, you fashion each alphabet of BREAK! into cell F4 to F9. At Format Cells dialog box, on the Alignment tab, pick-out Cpenetobjurgate for Insipid and Vertical. Then aim Font tab and fluctuate the font bigness to 22 and lion-hearted the deviation. Once you feel exhaustived all these treads, you should get a template deexpression that seems love Figure 3.6. Figure 3.6: Tyro season consultation template 22 Even though you may feel exhaustived this template deexpression on the is-suesheet, we accomplish nonproduction to diversify a brief bit alikeize on the smooth. Approximation Page Setup and then fluctuate the orientation to opinion. Mould abiding the tract bigness is A4. Next, we mould abiding that the rooms repairments are 2.5 for top and profound, 1.9 for left and straight, 1.3 for header and footer, and latestly Cpenetobjurgate on Page Horizontally. At Header/Footer tab, click on Use Header. At the cpenetobjurgate favoringity, click on edit Font trifle, fluctuate the font fashion to Batang, Lion-hearted and bigness 14. Then you fashion in My Season Table. Forthadvenient that, you click on Use Footer. At the cpenetobjurgate favoringity, you fashion in Definite Updated: and then instil spell and season. You should feel a sculpture preaim that seems love Figure 3.7. Figure 3.7: Season consultation sculpture preview Before you husband the smooth, you delete all other is-suesheets exclude the one thriveing a end our season consultation template. Then you tranquillityate the shuffle tab state to Season Table. Lastly, you husband this is-suebulk as a Template smooth fashion. The emanationion of an Excel Template is *.xlt. Tshort are numerous peels of template you can geneobjurgate and reuse them posterior such as resemblanceing alikeize shuffle, fashions, mathematic fashionula is-suesheets, adapted to state a few. 3.2 Cell Reference You feel already compasst cell relation from foregoing article. Recalling end encircling cell relation, a supmien epistle and a row sum identifies a cell consistently of the intersection. You can substantiate an locomotive cell relation from the State Box. Now we accomplish seem at the 3 fashions of cell relations and their definitions. 23 3.2.1 Relative, Arbitrary & Adulterated Cell References Cell Reference $A$10 Type of Reference Absolute reference Meaning A$10 Mixed relation $A10 Mixed relation A10 Relative reference Both supmien and row relations abide the corresponding when you resemblance this cell relation consistently they are arbitrary. This cell relation is adulterated. The support relation fluctuates when you resemblance this cell relation to another supmien consistently it is relative. The row relation does not fluctuate consistently it is arbitrary. This cell relation is adulterated. The row relation fluctuates when you resemblance this cell relation to another row consistently it is relative. The supmien relation does not fluctuate consistently it is arbitrary. Both supmien and row relations are not-absolute. When copied to another row and support, twain the row and supmien in the cell relation are prescribeed to animadvert the new colonization. Table 3.1: Cell References Next, we accomplish see how to use these 3 fashions of cell relation in Excel. We accomplish geneobjurgate a undesigning consultation of staff hire register. Fascinate thrive the counsels adown to geneobjurgate the register. 1) In cell A1, fashion in Global Tri-Tower Corp. 2) In cell A2, fashion in November – Marketing Employees’ Salaries List. 3) In cell A4 to H4, fashion Staff ID, Peculiar Name, Definite Name, Age, Gender, Base Pay, Commission Earned, Completion Income. 4) Starting from row 5 afront, fascinate penetobjurgate the axioms consistently by thriveing the consultation adown. MT01 MT02 MT03 MT04 MT05 MT06 MT07 MT08 MT09 James Bob Tony Grace Taphelo Ireen Muhammad Osamah Michelle Brook Arakton Leong Rabasha Maseko Tracey Saeed Abu Abdul Bashar Jefferson 29 23 22 47 36 35 34 49 34 Male Male Male Female Male Female Male Male Female 4000 2100 2100 5000 3800 4500 3500 5000 4500 Table 3.2: Global Tri-Tower Corp employees’ hire register 5) Unintermittently you feel exhaustived this register, in cell A15, fashion Commission Rate. 6) Fashion in 8% into cell B15. 24 7) Now, we accomplish provide the fashionulas and estimations for this register. In cell G5, fashion the fashionula of =F5*$B$15 8) In cell H5, fashion the fashionula of =F5+G5 9) Excellent cells G5 and H5 and then use the replenish wield to resemblance down the fashionulas to row 13. If you do not use arbitrary cell relation but not-arbitrary cell relation in tcomprehend 7, as you resemblance down the fashionula to the direct few rows, you accomplish get 0 computes. This is consistently say for cell G6, the fashionula would be =F5*B16 which is exhaustively wrong! 10) Now, we accomplish do fashionatting to this register. Peculiar of all, we associate and cpenetobjurgate A1 to H1. Then we growth the font bigness to 24, fluctuate Arial font to Abbey Medium Extended font and lion-hearted the denote. Prescribe row 1 summit to 70 pixels. Lastly we fluctuate the endground colour of cell A1 from colornear to yellow. 11) Next, click on Format Painter (mould abiding A1 is excellented) and then click and pull from A2 to H2. Fluctuate the font bigness from 24 to 16 on cell A2. 12) Vault Sketch machinebar. Excellent A1 and A2, click on Shadow Phraseology trifle on Sketch machinebar and click on Shadow Phraseology 6. 13) Fluctuate the supmien denotes from Arial font to Verdana font. 14) Now we accomplish prescribe the row and supmien summit and tediousness. Peculiar of all, prescribe row 2 to 50 pixels. Prescribe supmien A to 110, supmien B & C to 130, supmien D to 45, supmien E to 75, supmien F to 100, supmien G & H to 125. 15) Click on row 4 phraseology and straight click to approximation Format Cells. Go to Alignment tab and tick Infold Citation liberty. Add Infold Citation sign to cell A15 as polite-mannered-mannered. 16) Lion-hearted and add a proelevate brink to cell A4 to H4. 17) Add a publicity to cell F5 to H13 by clicking on the Publicity trifle from the Formatting machinebar. The publicity can be any reckonry’s publicity fashionat. 18) Add a crude colour endground to cell A15 and B15, fluctuate font colour to yellow, lion-hearted the cells, align left and definitely add Shadow Phraseology 6. 19) Approximation Page Setup, fluctuate orientation to opinion and cpenetobjurgate on page insipidly and uprightly. After exhaustived these 19 treads, you should adaptedty a is-sueshuffle that seems love Figure 3.8. 25 Figure 3.8: Global Tri-Tower Corp hire register usageary is-sueshuffle aim Press CTRL+` to switch the usageary is-sueshuffle aim to fashionula is-sueshuffle aim or Formula Auditing Mode. A fashionula is-sueshuffle aim should seem love Figure 3.9 thriveing you feel prescribeed the supmien width. Figure 3.9: Global Tri-Tower Corp hire register fashionula is-sueshuffle aim The fashionula is-sueshuffle aim is an twinklingous aim to profession Excel users of all the conspicuous cells’ deviation. If you excellent a cell, you accomplish see what was fashiond onto the cell that defenses on fashionula bar accomplish be alove on the is-suesheet. The alikeize twinklingous experience of Formula Auditing Mode is to celebobjurgate round on fashionulas, insistencies of cell relations and the colonizations of cell relations of a sign fashionula on a excellented cell. Figure 3.10: Cell connectencing colours of a fashionula 26 3.2.2 3-D Cell Reference So far the cell relations that you feel seen and used so far are in-effect “2-D” cell relations. Why we fawn it as 2-D? Simple, the cells that you connect to feel X-axis and Y-axis which are supmien and row. We accomplish now seem at what is 3-D cell relation. In Excel, a 3-D cell relation is stated by is-sueshuffle state thriveed by cell relation. An cry impression ‘!’ disconnecteds is-sueshuffle state from cell relation. In enjoin to do a undesigning 3-D cell relation in a is-suebook, the is-suebulk must feel at balanceest 2 is-suesheets. E.g. the is-suebulk has Sheet1 and Sheet2. In Sheet1, cell A1 has compute of 20. Excellent cell B2 in Sheet2 and fashion in =Sheet1!A1 and lean Penetobjurgate key. An choice way to engagementinate this is by resemblanceing cell A1 and then you straight click on cell B2 in Sheet2. Forthadvenient that, you pick-out Paste Special and then click on Paste Conjoin trifle. Paste Conjoin bid effortlessally add a cell relation. In this in, the cell relation is a 3-D cell relation. If you mould any fluctuates to A1 in Sheet1, B2 in Sheet2 accomplish confer-upsingly fluctuate. We accomplish see how to use 3-D cell relation in the direct subject-subject – 3.2.3 Consolidating Cell Reference. 3.2.3 Consolidating Cell Reference Once you feel adownstood the proposal of cell relation and 3-D cell relation, we accomplish devote the concepts onto the foregoing is-sueshuffle (we coveting that you feel husbandd the smooth). To consolispell axioms, you coalesce the computes from diverse strolls of axioms, which you accomplish nonproduction to use incongruous fashions of cell relations. 1) We accomplish provide off by renaming Sheet1 to November. Then we delete other is-suesheets. 2) Straight click on November shuffle tab and then pick-out Affect or Resemblance liberty. We chose affect to end liberty and geneobjurgate a resemblance. 3) Geneobjurgate another resemblance of November shuffle tab and affect it to the end. 4) Restate the peculiar resemblance to December and the relieve resemblance to Summary. 5) Aim December is-suesheet. Fluctuate the November in cell A2 to December and fluctuate 8% to 11%. 6) Aim Abridgment is-suesheet. Excellent F and G supmien phraseology, straight click and pick-out delete. 7) Delete row 15. 8) Fluctuate cell A2 resigned to Marketing Employees’ Salaries Summary. 9) In cell F5 of Abridgment is-suesheet, you can thrive one of the two treads to consolispell axioms. a) Fashion =SUM(, then click on November shuffle tab, click on cell H5, lean and redeep down Imsever key, click on December shuffle tab and then fashion ) to vocableinate the fashionula. Lean Penetobjurgate key. 27 b) If you recognize the cell relations and is-sueshuffle states, you can fashion =SUM(November:December!H5). 10) Either mode accomplish adaptedty a compute of 8,760.00 in cell F5. 11) Lastly, add a Sum office at cell F14 and then add a top and incork proelevate brink. Refawn end Article 2, the colon ( : ) that situates in natant of 2 cell relations states that this is a stroll of cell relations. The colon applies the tallying concept to is-sueshuffle states. The fashionula alikeize uses Sum office to add up the compute of H5 of November thriveing a end H5 of December. In other enjoins, the Sum office adds up all the computes of H5 cells from the stroll of is-suesheets, provideing from November plow December. A enjoin of premonition though, doing this peel of consolispell axioms can aim spherical relation if you are not circumspect. How it may supervene, atcontribute the thriveing in. A is-suebulk has 3 shuffles whereby shuffle1 and shuffle3 cell A1 has compute of 10 and 20. In shuffle2 cell A1, you do a consolispell axioms fashionula of =sum(sheet1:sheet3!A1). If you seem at the fashionula circumspectly, it is amply adownstood that cell A1 from shuffle1 to shuffle3 accomplish be adventitious up coincidently. On vocableinater superintendence, if cell A1 of shuffle2 sojourns this fashionula, it media shuffle1!A1 + shuffle2!A1 + shuffle3!A3 and the compute is settled onto shuffle2 of cell A1. It does not mould any notion at all! Thus, it is fawned spherical relation. Lastly, consolispell cell relation can be obstructly any peel of fashionulas as hanker as it complicates alikeize than 1 fashion of cell relations. Atcontribute the thriveing fashionula that is settled onto cell C5 of Sheet1. =A1 + Sheet2!A4 * Sheet3!$A$1 The fashionula alikeize is an in of consolispell cell relation fashionula. 3.3 Advance Formatting Cells We accomplish seem at a few fashionatting repairments that may improve your is-suesheets. As you recognize, we can use the trifles at the Formatting machinebar to do undesigning fashionatting on excellented cells. Excel offers Format Cells sign to improve the cells aid. Tshort are a few ways to approximation to Format Cells dialog box. 1) Format Cells can be amply approximation by urgent-compulsory CTRL+1 keys coincidently. 2) You so can approximation it by urgent-compulsory Format Cells trifle on the Formatting machinebar. 3) You can approximation it from Format menu Cells…command. 4) Lastly, you can straight click on excellented cell(s) and then pick-out Format Cells bid. Firstly, we seem at the Sum tab. It offers a lot of bids and offices that treat digits. Tshort are 12 incongruous fashions of categories as professionn in Figure 3.11. 28 Figure 3.11 Sum tab The thriveing consultation professions the unconcealed cognomen of each classify of Sum tab. Number Formats Comparison Category Description General Currency No local sum fashionat Default of two decimal settles; can so vault commas for thousand separators Aligns publicity cast, vaults two decimal places and comma separators Displays serial equipollent of spell Displays serial equipollent of season Multiplies compute by 100 and vaults the adaptedty forthadvenient a end % expression Displays decimal element of compute as a fraction Displays the sum in or-laws notation Text fashionat cells are treated as citation flush when a sum is in the cell. The cell is vaulted precisely as penetrateed Accounting Date Time Percentage Fraction Scientific Text Default Display (Value – 1054.253) 1054.253 $1,054.25 $ 1,054.25 11/19/1902 11/19/1902 6:00AM 105425% 1054 1/4 1.05E+03 1054.253 Table 3.4: Sum fashionats comparison 29 So far, these comparisons adapted profession the basic of the sum fashionats. Undeniable categories feel concomitant repairments to confess you to pick-out alikeize fashions for that sign classify. When we use them in the rectify top, they can be a potent fashionatting machine. The Citation classify is beneficial in undeniable tops such as vaulting the straight input of a Malaysian IC superveneing a endin a cell. The Special classify is unbeggarly consistently it insists on what peel of is-sue you are established on. Special fashionats are beneficial for rounding register and axiomsbase computes of incongruous reckonries fashionats. The Use fashionat is a capricious classify that confesss user to geneobjurgate obstructly any peel of fashionatting to excellented cell(s). The thriveing consultation delineates the fashionat edicts of Use fashionat. Custom Format Table Format Edict Use # 0 ? , * “text” _ ; , , = [Color] @ Displays expressionificant digits, but not inexpressive naughtes Displays inexpressive naughtes if a sum has fewer digits than the sum of naughtes profital in the fashionat Adds quantitys for inexpressive naughtes to cord up decimals; so used for fraction fashionats thriveing a end deviateing gum of digits Thousands separator Repeats the direct missive in the fashionat edict to replenish any utter quantitys in a cell Inserts any citation thriveing a endin the quotes as sunderneath of the sum fashionat Displays a uncollectively missive as sunderneath of the sum fashionat Inserts a quantity missive in the sum fashionat Separates favoringitys of a use sum fashionat; each fashionat can feel lewd favoringitys to fashionat agricultural, indirect, naught, and citation computes. Subject operators that devote the use fashionat barely if a numerical compute coalesces the profital circumstance Use one of eight colors (Black, Blue, Cyan, Green, Magenta, Red, White, and Yellow) to vaults computes; colors must be registered peculiar in a favoringity Used as the definite minute in a use sum fashionat to vault citation; if this cast is omitted from the use fashionat, any citation penetrateed is not vaulted Table 3.5: Use fashionat Next, we veer our circumspection to the Alignment tab. This tab offers celebrity that accomplish veer a evident turbid citation orientation to celebrity choice. This tab has citation alignment, citation use, citation superscription, and citation orientation as professionn in Figure 3.12. The citation alignment is beggarly sign that aligns citation suitably. The citation use has 3 offices, infold citation, reweary to fit and associate cells. • Wrap citation – This sign affects sentence/citation that is too hanker onto the direct cord of the tallying cell. It is incongruous from urgent-compulsory ALT+ENTER to imsever the citation to the direct cord. • Shrink to fit – This sign retires/reduces the font bigness that accomplish fit nicely according to supmien width. • Merge cells – This sign associate excellented cells into one cell. It is sunderneath of the Associate and Cpenetobjurgate bid. 30 Consider the orientation of quantitys. It is beneficial to circulate a citation from 90° to -90°. But you feel to use this 180° quantity of orientation thriveing a end regard consistently if you devote this adaptedty to cells that feel brinks, you accomplish see celebrity ill-humored. For in, we devote 45° to Monday plow Saturday cells of Tyro Season Consultation template, it accomplish seem love this (connect to Figure 3.13). Figure 3.12: Alignment tab Figure 3.13: 45° orientation on cells thriveing a end brinks The updirect citation orientation can be very beneficial for Asian dictions and citation such as Chinese, Korean and Japanese writings. For in, we can fluctuate the B R E A K ! to Lunch Smash for each cell, impoverish font bigness, infold citation and then use updirect citation orientation. It accomplish seem love Figure 3.14. Figure 3.14: Updirect citation orientation 31 Now we seem at Font tab. The Font tab confesss user to treat font in numerous ways. This tab has a Preaim favoringity to vault the fluctuates of the font repairments. The Undercord favoringity has 4 incongruous fashions of adownline, which is disconnected into 2 bunchs: resemblanceing and non-accounting phraseologys. You can fluctuate font colour on this tab as polite-mannered-mannered. Figure 3.15: Font tab The lewdth tab is the Brink tab as professionn in Figure 3.16. Naturally, this tab confesss user to treat the brinks of excellented cells. The Presets favoringity has 3 trifles that confer-upsingly add or reaffect brinks to the cells. The Brink favoringity is-sues coincidently thriveing a end Cord Style. Brink favoringity confesss you to useize the brink defenseance. The Cord Phraseology has unundeniable cords schemes. To add brinks to cells, pick-out a cord phraseology peculiar, and then you click on the trifles either on Presets or Border. You can add colours to the brinks as polite-mannered-mannered. Figure 3.17 professions the resigned of Patterns tab. This tab confesss user to add colours to cell endground or precedents to cell endground. A recttransmould preaim box professions the exemplification of the clarified colour shading or deexpression precedent. In numerous plights, not numerous community use precedent thriveing a end citation consistently you can obdurately comprehend the citation if the font bigness is too inexpressive. 32 Figure 3.16: Brink tab Figure 3.17: Patterns tab The definite tab is the Protection tab. This sign confesss user to protect/lock cells of an Excel is-suebook. This tab confesss the user to state whether the excellented cell(s) should be locked or obscure from aim. 33 3.3.1 Subject Formatting So far, all the fashionatting that we feel seen and used is adapted the usageary fashionatting that we devote manually. In Excel, tshort is subject fashionatting that the cells accomplish fluctuate its fashionat the avail it coalesces undeniable circumstance. In other enjoins, the circumstance is penny and then the fashionat fluctuate consistently. Let’s feel a seem at the thriveing in of how to use subject fashionatting. Say, we nonproduction to devote red colour endground to cells that redeep sum near than 50. We devote crude colour endground to cells that redeep sum elder than 89. Seem at Figure 3.18 and fashion in those axioms into those cells. Figure 3.18: Tyro impressions register Then, you excellent cell B2 to B4 and then click on Format menu Conditional Formatting bid. You should see the Subject Formatting dialog box (Figure 3.19). Operator Preaim fashionat Conditional fashionat Figure 3.19: Subject Formatting The left most ooze down register is the fashion of circumstance fashionat you nonproduction to devote to excellented cells. The relieve ooze down register from the left is the operator. It is adapted when the subject fashionat is Cell Compute Is liberty. The arenas to the straight deviate when you pick-out incongruous fashion of operator. Those arenas can redeep citation, digits and cell relations. The “No Format Set” colornear box is to preaim the fashionat to use when circumstance is penny. The Format trifle, when clicked, it accomplish induce up the Format Cells dialog box thriveing a end 3 tabs barely: Font, Brink and Patterns. The Add trifle confesss you to add up to 3 circumstances to excellented cells. The Delete trifle is to delete 1 – 3 circumstances from excellented cells. Straight now, we accomplish add in circumstance fashionat onto the cells. Peculiar of all, we fluctuate natant operator to near than operator. Fashion 50 into the arena. Forthadvenient that, we add another circumstance and fluctuate the operator to elder than and the compute is 89. Next, click on the peculiar Format trifle and then fluctuate the cell shading to red colour. Click Ok. Lastly, click on relieve Format trifle and then fluctuate the cell shading to crude colour and lion-hearted it. 34 After that, you fashion in 10, 50 and 100 into the three cells to see the adaptedtys for yourself. Subject fashionatting is beneficial when you nonproduction Excel to highporconsultation cells that coalesces a circumstance. 3.4 Navigating in Excel So far, you must feel navigating in Excel using a few undesigning key strokes on the arrow keys and mouse clicks. However, tshort are seasons that it is alikeize beneficial to straggle Excel is-suebulk using the keyboard. The thriveing consultation delineates the navigation keys. Navigating in is-suebulk using the keyboard To affect Press Up one cell Down one cell Right one cell Left one cell The UP ARROW key The DOWN ARROW key The RIGHT ARROW key or TAB key The LEFT ARROW key or SHIFT+TAB keys The HOME key The END key and then the ENTER key The PAGE DOWN key The PAGE UP key The CTRL+HOME keys The CTRL+END keys or the END key and then the HOME key The CTRL+ARROW keys To peculiar locomotive cell of the popular row To the definite locomotive cell of the popular row Down one page Up one page To cell A1 To definite cell retaining axioms in a worksheet To aspect of the definite cell retaining a compute or to the aspects of a is-suesheet Figure 3.6: Navigation keys Summary Template is a very beneficial smooth that confesss user to reuse it alikeize and alikeize intermittently thriveing a endout the nonproduction to geneobjurgate the tallying is-sueshuffle deexpression and fashionatting. Tshort are 3 fashions of cell relations and each of it serves a balanceing. 3-D cell relation connects a cell from another is-suesheet. Consolispell axioms cell relation complicates numerous incongruous fashions of cell relations to engagementinate a estimation to beseem a fashionula. By compassing and perception Format Cells experience, one can treat and fashionat the completion is-sueshuffle to seem alikeize exhibitable. Subject fashionatting is beneficial consistently you set the circumstances that you let Excel fluctuate the fashionatting effortlessally when circumstances are met. Definite but not balanceest, navigating encircling a is-suebulk using keyboard can be unswervinger than using mouse clicks. 35 Chapter 4 Graphs and Charts 4.1 What is a Chart? 4.2 Chart Types 4.3 Creating a Chart using Chart Wizards 4.4 Pick-out Chart Type 4.5 Curb Axioms Series 4.6 Exhaustive Chart Options 4.7 Chart Location 4.8 Modifying Chart 4.9 Enhancing Chart 4.10 Moving and Resizing Chart 4.11 Multiple Axioms Series 4.12 Aim Stringing and Embedding OBJECTIVE In this article, tyro accomplish compass: Describe how a chart can be used to impart-up a missive List diverse fashions of charts and delineate the balanceing of each See natant an embedded chart versus a chart in its own shuffle Use the Chart Necromancer to geneobjurgate and diversify a chart Use the Sketch machinebar to improve a chart by adding cords and aims See natant axioms succession in rows versus supports Differentiate natant a stacked-supmien chart versus a severy-by-party supmien chart ? Geneobjurgate a Enjoin instrument that is conjoined to a is-sueshuffle and an associated chart ? ? ? ? ? ? ? 36 4.1 What is a Chart? A chart is a picturesqueal stateation of axioms in a is-suesheet. Charts are visually appealing and mould it not-hard for users to see comparisons, precedents, and trends in axioms. For entreaty, rather than having to excite diverse supports of is-sueshuffle gum, you can see at a glance whether sales are progress or mound alikeize quarterly ends, or how the developed sales parallel to the projected sales. Charts feel the thriveing elements: • • • Category labels – illustrative citation entries Data aims – numeric computes Data succession: a bunching of axioms aims We accomplish seem into the three elements in a end. Next, we affect on to the chart fashions. 4.2 Chart Types Tshort are numerous fashions of charts adapted in Excel: • • • • • • • • • • • • • • Column Bar Line Pie XY (Scatter) Area Doughnut Radar Surface Bubble Stock Cylinder Cone Pyramid Each fashion has its own missiveistics and experience. Therefore you feel to pick-out and use the delayredeep chart fashion to state your axioms. For in, you use pie and spurious pie charts vault proportional relationships; you use supmien charts vault gum rather than percentages; bar charts vault gum insipidly. No subject what fashion of charts you nonproduction to apprehend into Excel is-suebook, intellect to celebobjurgate it undesigning. The thriveing professions the deexpression and seems of charts. 3500 3000 2500 Beverages Food Accessories 2000 1500 1000 500 0 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 37 Figure 4.1: Supmien chart 4th Qtr 3rd Qtr Accessories Food Beverages 2nd Qtr 1st Qtr 0 1000 2000 3000 4000 Figure 4.2: Bar chart 3500 3000 2500 Beverages Food Accessories 2000 1500 1000 500 0 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Figure 4.3: Cord chart Total Profit/Loss RM1236.00, 24% (RM530.00), 10% RM432.00, 8% 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr RM3049.00, 58% Figure 4.4: Pie chart 38 8000 6678 6000 4736 4000 2000 3232 2656 2363 1654 1263 0 0 1 2 Beverages 2146 1320 324 1142 233 3 Food 4 5 Accessories Figure 4.5: XY (Scatter) chart 10000 8000 Accessories Food Beverages 6000 4000 2000 0 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Figure 4.6: Area chart Total Income 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Figure 4.7: Doughnut chart 39 Beverages 8000 6000 4736 3232 4000 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 1263 2000 233 0 324 1142 1320 1654 2146 2363 2656 6678 Accessories Food Figure 4.8: Radar chart 8000 6000 4000 2000 Accessories 0 6000-8000 4000-6000 2000-4000 0-2000 Food 1st Qtr 2nd Qtr Beverages 3rd Qtr 4th Qtr Figure 4.10: Surface chart 7000 6000 5000 Beverages Food Accessories 4000 3000 2000 1000 0 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Figure 4.11: Cylinder chart 40 7000 6000 5000 Beverages Food Accessories 4000 3000 2000 1000 0 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Figure 4.12: Cone chart 7000 6000 5000 Beverages Food Accessories 4000 3000 2000 1000 0 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Figure 4.13: Pyramid chart 4.3 Creating a Chart using Chart Wizards Chart Necromancer is a tcomprehend by tcomprehend counsel to use user to geneobjurgate a chart. Now, we accomplish provide to geneobjurgate a chart naturalized on Article 2 Makanlah! Limkokwing, Cyberjaya – January 2006 Sales is-suebook. Fascinate thrive the counsels adown to geneobjurgate a supmien chart. 5) Excellent cell A4 to E8 and then click on Chart Necromancer trifle on Banner machinebar. 6) Pick-out the highlighted sub-chart fashion as professionn in Figure 4.13. Then click Next. 7) In Tcomprehend 2, aim Succession Tab. Then click on Series1 and then click on Name field. End the cursor is bstringing on the State arena, you click on cell B3. 8) Forthadvenient that, you click on Series2 and you accomplish see that Series1 fluctuated to Week1. You endure iterateing the tallying counsels as alikeize exclude you click on C3, then D3 and definitely E3 for Series2, Series3 and Series4 respectively. Unintermittently you are executed, click Next. 9) In Tcomprehend 3, you add a Chart Demusic that is straightly alove to consultation denote. Fashion Stall for X axis and Compute for Z axis. 41 10) Click on Marvellous-story tab and settle the marvellous-story at the profound. 11) Click on Axioms Labels and excellent Compute liberty. Then click Next. 12) Settle as aim in Sheet1 for the definite tcomprehend of Chart Wizard. Pick-out any one of the adapted subtype, but changes depconposteriority on chart fashion Select any of the chart types or click Custom Types to geneobjurgate your own Description encircling chart sub-type To keen preaim your chart Figure 4.14: Chart Necromancer tcomprehend 1 Once you feel exhaustived these Chart Necromancer treads, you should get a chart that seems love Figure 4.14. It is very obduobjurgate to comprehend the chart consistently the undivided chart is too inexpressive. Thus, we nonproduction to fashionat the completion chart aim. Makanlah! @ Limkokwing, Cyberjaya January 2006 Sales 10000 7854 6341 5654 7864 5643 67656876 4324 4567 4356 5889 Value 50005673 4215 4564 3244 3672 5535 4256 3573 1235 0 Malay Western Stall Week1 Week2 Week3 Week4 Figure 4.14: Unformatted supmien chart Usually, we do not nonproduction to settle the chart as aim onto an penny workshuffle but preferably settle it on a new is-suesheet. It is all up to you to diversify the chart to the way you nonproduction it to seem love. A fashionatted supmien chart is professionn in Figure 4.15. 42 M akanlah! @ Limkokwing, Cybe rjaya J anuary 2006 Sale s 8000 6876 5889 6765 3244 4356 4256 5654 4564 5535 5643 4567 4215 3672 3573 5673 4324 5000 V a l 4000 u e 3000 6341 6000 7854 7864 Best stall natant the tranquillity 7000 1235 2000 1000 0 Malay Chinese Week1 Indian Stall Week2 Week3 Western Beverage Week4 Figure 4.15: Formatted supmien chart 4.4 Pick-out Chart Type Referring to Figure 4.13, tshort are 2 tabs, Banner Types and Use Types. Banner Types redeep all the chart fashions mentioned in 4.2 Chart Types. Each chart fashion has diverse sub-types. Each sub-fashion has its cognomen and scheme. You nonproduction to pick-out an delayredeep sub-fashion for your axioms. You use the Lean and Redeep to Aim Exemplification trifle to confer-upsingly preaim how your chart may seem love. If all you see is a utter curtain, blot-out and reexcellent axioms. 4.5 Curb Axioms Series Figure 4.16 professions Chart Necromancer Tcomprehend 2 of 4. In this tread, we curb the axioms succession and mould any compulsory prescribement to the axioms that we foregoingly excellented. Tshort are 2 tabs, Axioms Stroll and Series. The axioms stroll professions you the 3-D cell relation of the stroll of axioms you feel excellented precedently you click on Chart Necromancer trifle. If you nonproduction to prescribe the axioms stroll, you click on the subsidence trifle and then aim end the is-suebulk and pick-out the axioms stroll intermittently. The Succession in favoringity vaults the computes in bars insisting on which succession you pick-out, row or support. In Figure 4.16, we chose supports and it accomplish vault 4 incongruous keys colours on the marvellous-storys consistently we feel 4 supports of axioms. Adown the chart, the X-axis labels are naturalized on the incongruous row phraseologys. If we feel clarified rows, then we would feel 5 incongruous keys colours on the marvellous-story and then the Xaxis labels accomplish be naturalized on the incongruous supmien phraseologys. However, you may feel mentiond that the marvellous-story keys states are not mediocreingful at all. Therefore, we nonproduction to fluctuate them into the rectify states by aiming Succession tab. By seeming at Figure 4.17, we can see Succession favoringity, State arena, Values arena and Classify (X) axis labels favoringity. The Succession favoringity professions you the state of the marvellous-storys. If they are inaccurate, you nonproduction to fluctuate them one by one by excellenting the succession and then fashion in / instil 3-D cell relations to the State arena. Each of succession has a stroll of computes and it is usually 3-D cell relations. Lastly, the classify axis labels deviate insisting on the fashion of 43 chart you feel clarified in tcomprehend 1. Rarely it is X-axis, rarely it is Y-axis and rarely it is Z-axis. Preaim the chart precedently going aid Bars defense insists on choosing axioms naturalized on columns or rows The stroll of cells we excellented precedently clicking on Chart Wizard Collapse trifle screens the dialog and confesss you to excellent a incongruous stroll Figure 4.16: Chart Necromancer tcomprehend 2 axioms stroll tab 44 Default succession names for marvellous-story that nonproduction to be changed Label the succession short X-axis labels Range of computes of excellented succession Cell relation of X-axis labels Figure 4.17: Chart Necromancer tcomprehend 2 succession tab 4.6 Exhaustive Chart Options Step 3 is the Chart Options. This tcomprehend has 6 tabs (but it may varies from incongruous chart fashion), Titles, Axes, Gridlines, Legend, Axioms Labels and Axioms Table. The Titles tab confesss you to label the chart demusic and the axes. The Axes tab confesss you to pick-out which axis as the leading and to profession/screen computes of Y/Z axis. The repairments in this tab so deviate insisting on the chart fashion. Gridlines tab confesss you to profession/screen elder and undignified gridlines of classify X, succession Y and compute Z axes. The Marvellous-story tab confesss us to profession/screen marvellous-storys and prescribe its settlement. The Axioms Labels tab professions us to add labels to the chart. We can profession succession state, classify state, compute, percentage and dream bigness; insisting on what peel of chart we feel used. The definite tab is Axioms Consultation tab that either profession or screen the axioms consultation adown the chart. Usually, we do not add axioms consultation to charts. 4.7 Chart Location This is the definite tcomprehend of Chart Wizard. You can either settle the chart as new is-sueshuffle or settle it on an penny is-suesheet. However, placing on penny is-sueshuffle may mould the chart seems inexpressive and hard to comprehend. 45 4.8 Modifying Chart Once a chart has been settled onto a new is-sueshuffle or as an aim on an penny is-suesheet, rarely you may nonproduction to diversify it. Tshort are numerous ways to diversify a chart and we can use the Chart machinebar to diversify it. The Chart machinebar has diverse trifles (from left to straight): Chart Objects, Format Chart Area, Chart Type, Legend, Axioms Label, By Row, By Column, Transmould Clockwise and Transmould Counter Clockwise. It is abundant easier to diversify the chart by using this machinebar rather than clicking and straight clicking on chart labels to diversify them. It is twinklingous to fix that the axioms labels are comprehendable if you apprehend them on the chart consistently it would aggravatecome the balanceing to settle them tshort unreadable. Figure 4.18: Chart machinebar 4.9 Enhancing Chart No chart can be whole thriveing bybygone through the 4 treads of Chart Wizard. Therefore we nonproduction to improve the chart defenseance alikeize exhibitable. You can improve a chart by using the Sketch machinebar (shown in Figure 4.19) to add concomitant patterns and citation to delineate the chart. Bear in intellect, do not alikeize improved the chart thriveing a end too numerous patterns consistently it would “block” some recognizelaspect on the chart. Hence, you feel to add them thriveing a end foresight. Figure 4.19: Sketch machinebar 4.10 Moving and Resizing Chart Sizing wields state a chart is excellented and can be affectd, sized, copied, or deleted Corner wields state bar can be edited Figure 4.20: Moving and resizing chart aims 46 In numerous plights, we may nonproduction to affect and rebigness undeniable power of the chart. Referring end to Figure 4.14 and Figure 4.15, we affectd and resized denotes, axioms labels and the 3-D walls. Seem at Figure 4.20, tshort are wields at chart recesss to state that the chart can be rebigness and affect encircling. Bars cannot be resized and affectd but they can be deleted. Other fashion of charts such as pie charts, you can excellent a slide of the pie and affect it out from other elements. 4.11 Multiple Axioms Series The chart that we generated adapted now has multiple axioms succession excellented. By excellenting multiple axioms succession, you see favoring axioms aims rather than completions. When we pick-out one set of axioms succession from is-suesheet, it can be either row or support, the axioms aims are plotted the tallying either way, but the bunching accomplish be incongruous. If axioms succession are in rows, the peculiar row is used for classify labels on the chart. The abideing rows are used for axioms succession. The peculiar supmien accomplish be used as marvellous-story keys. If axioms succession are in supports, the peculiar supmien is used for classify labels on the chart. The abideing supports are used for axioms succession. The peculiar row accomplish be used as marvellous-story keys. 4.12 Aim Stringing and Embedding So far, we barely feel charts embedded as aims superveneing a endin Excel is-suebook. It would be rectify to feel those charts defense in other impressions such as Microsoft Enjoin and Microsoft PowerPoint. Tshort are two ways to settle an aim of one impression to another impression: embedded aim and conjoined aim. An embedded aim is abundanced in the instrument. For in, an Excel chart beseems sunderneath of the Enjoin instrument. That chart can be edited as a sketch aim if you adapted use resemblance the chart from Excel and paste it into Word. Any fluctuates made to this chart in Excel do not fluctuate in Enjoin and badness versa. A conjoined aim is abundanced in its peculiar rise smooth but geneobjurgate an mienraiture smooth in the other impression. To engagementinate this mode, you resemblance the chart from Excel, when you nonproduction to paste it into the other impression, you use Paste Special bid (from Edit menu) and Paste Conjoin it. Any fluctuates made to the chart in Excel accomplish effortlessally fluctuate the conjoined aim in the other impression. A conjoined aim has an liberty to upspell and synchronize thriveing a end its deep instrument. Figure 4.21 professions the Links dialog box of Microsoft Word. A conjoined aim from one impression to another impression has upspell rise, unconcealed rise, fluctuate rise and smash conjoin libertys. The mode of updating the conjoined aims can be manual or effortless. 47 Figure 4.21: Stringing aim Summary Charts are picturesqueal stateation of axioms. Tshort are numerous fashions of charts in Excel. Each chart has diverse sub-fashion charts. You excellent the chart fashion naturalized on the missive you are arduous to consign. It is easier to geneobjurgate a chart by using Chart Wizard. Charts can be embedded on a disconnected chart shuffle. Multiple axioms succession may be profital in either rows or supports. Corresponding axioms aims, but incongruous bunching imparts incongruous comparison. Aim Stringing and Embedding (OLE) generates a unification instrument. It sojourns aims from multiple impressions. By embedding the aim, it is abundanced thriveing a endin the unification instrument. By conjoining the aim, it is abundanced as a disconnected smooth. 48 Chapter 5 Decision Making in Spreadsheets 5.1 PMT Function 5.2 FV Function 5.3 Aim Seek 5.4 Statistical Functions 5.5 IF Function 5.6 VLOOKUP & HLOOKUP Functions OBJECTIVE In this article, the tyro accomplish compass: ? Use the PMT office to proelement the blot-outment of a car advance or advance ? Use the FV office to state the advenient compute of a recess resemblance ? Exevident how the Aim Prosper bid facilitates the resolution-making process ? Use the AVERAGE, MAX, MIN, and COUNT offices ? Use the IF, VLOOKUP and HLOOKUP offices to instrument rekey making 5.1 PMT Function Banks unconcealedly set reciprocations on advances and advances in tallyent blot-outments alikeize a agricultural end of season. Natant these usageary blot-outments the mix of intetranquillity and foremost fluctuates as season byes. A uncollectively fashionula can amply proelement the reciprocations on a advance of $x,000 at y% alikeize z years. The genuine cosmos-people is frequently alikeize entangled and intetranquillity objurgates fluctuate at ircustomary intervals, frequently sunderneath of the way through a sign recancelment end. Let’s seem at one in, a sum of $50,000 advance is borrowed and to be repaid alikeize 1 year in tallyent monthly blot-outments at an intetranquillity objurgate of 7%. The Excel PMT() office is used to proelement monthly reciprocation, endic blot-outments such as car or advance blot-outment. Its syntax is: 49 PMT(Rate, Nper, Pv, Fv, Type) • • • • • Rate – The intetranquillity objurgate per end for the advance. Nper – The completion sum of blot-outment for the advance. Pv – The exhibit compute: the completion quantity that a succession of advenient blot-outment is rebuke now. Fv – The advenient compute, or a capital alikeize you nonproduction to comby thriveing the definite blot-outment is made, 0 (zero) if omitted. Type – A argumentative compute: blot-outment is due at the providening of the end = 1; blot-outment is due at the end of the end = 0 or omitted. Referring end to the in alikeize, the intetranquillity objurgate should tell to uncollectively recancelment end and not the annual objurgate. In this in, it is 1/12th of 7%. Now, let’s establish up a undesigning register to proelement a advance as professionn in Figure 5.1. Figure 5.1: PMT estimation If you seem circumspectly at the fashionula of B7, you see a indirect ( - ) operator in face of PMT fashionula consistently by lapse Excel accomplish vault the recancelment as indirect quantitys. Thus, by placing a indirect operator accomplish transmute the indirect quantity to agricultural quantity. Forthadvenient a end PMT office and a spreadsheet, we can mould self-assertions by placing some other potential axioms into the cells and let the impression does its estimation. Let’s add in a few alikeize self-assertions as professionn in Figure 5.2. We adventitious alikeize axioms into the consultation. We geneobjurgate 4 self-assertions whereby we used the tallying axioms exclude that we feel 2 incongruous scenarios short whereby the peculiar is blot-outment thriveing a end due spell at the providening and the relieve is blot-outment thriveing a end due spell at the consequence. The fashionula that we used in cell C8 is: =-PMT($B$7/$B$6,C$5*$B$6,$B$4,0,$B8) Since you feel already compasst cell relations, you accomplish mention that the fashionula used arbitrary cell relation and adulterated cell relations. When we resemblance this fashionula by using replenish wield to neighboring cells, the cells accomplish fluctuate, proelement and adaptedty the rectify compute. 50 Figure 5.2: PMT self-assertions 5.2 FV Function In Excel, the FV office profits the advenient compute of an bombardment naturalized on an intetranquillity objurgate and a trustworthy blot-outment catalogue. The syntax for the FV office is: FV( objurgate, nper, pmt, [pv], post ) • • • • • Rate – The intetranquillity objurgate for the bombardment. Nper – The completion sum of blot-outment for the annuity. Pmt – Payment, the quantity of the blot-outment made each end. Pv – The exhibit compute of the blot-outment. It is libertyal. If omitted, the FV office assumes PV is 0. Type – It states when the blot-outments are due. It is libertyal. Fashion can be one of the thriveing computes: blot-outment is due at the providening of the end = 1; blot-outment is due at the end of the end = 0 or omitted. Let’s atcontribute this in to use FV office. You nonproduction to recognize the advenient compute of an bombardment wshort you regularty $10,000 into a frugals resemblance that earns 8.0% perfect-year. You are going to regularty $1,000 at the providening of the month, each month, for 3 years. Now, let’s establish up a undesigning register to proelement the bombardment as professionn in Figure 5.3. Figure 5.3: FV estimation 51 You must feel mentiond that tshort is indirect operator in face of PMT and PV parameters consistently you are making the blot-outment, thus it has to be indirect values. Similarly to foregoing PMT office, we so can mould self-assertions for FV office. Let’s add some potential axioms into the register and improve the is-suesheet. We geneobjurgate 4 self-assertions whereby we used the tallying axioms exclude that we feel 2 incongruous scenarios short whereby the peculiar is usageary blot-outment thriveing a end due spell at the providening and the relieve is usageary blot-outment thriveing a end due spell at the consequence. Figure 5.4 professions the new self-assertion register. The fashionula that we used in cell C8 is: =FV($B$5/12, C$7*12, -$B$6, -$B$4, $B8) Figure 5.4: FV self-assertions 5.3 Aim Seek Sometimes, when we geneobjurgate self-assertions, we recognize the input computes but not lacriterion computes. Hence, we use offices and fashionulas. However, what if we do not recognize the input compute(s) but we recognize the lacriterion computes? How do we is-sue end to recognize the input compute(s)? In Microsoft Excel, tshort is this office fawned Aim Seek. Aim Prosper is sunderneath of a suite of bids rarely fawned what-if separation machines. When you recognize the hanker-ford adaptedty of a uncollectively fashionula but not the input compute the fashionula nonproductions to state the adaptedty, you can use the Aim Prosper sign adapted by clicking Aim Prosper on the Tools menu. When aim forthcoming, Microsoft Excel varies the compute in one local cell until a fashionula that's insistent on that cell profits the adaptedty you nonproduction. Let’s seem at Figure 5.5 to see how Aim Prosper is-sues. Peculiar of all, you fashion in the thriveing citation and sum trustworthys into the cells and then add the PMT office. The debate we feel 2 supports, 1 precedently aim forthcoming and 1 thriveing aim forthcoming is to profession you how aim prosper office does the estimation to get the adaptedty. To use aim prosper: 1) Excellent cell C6, click on Tools menu Goal Prosper bid. 2) Connect to Figure 5.6 to get axioms entries for Aim Prosper dialog box. 3) Click Ok and then you accomplish see Aim Prosper does a very unswerving estimation and adaptedty a adaptedty for cell C5 as professionn in Figure 5.7. 52 Figure 5.5: Aim Prosper estimations Figure 5.6: Aim Prosper dialog box Figure 5.7: Aim prosper adaptedty Please imsever music that the Set cell arena requires a cell relation thriveing a end fashionula. The To compute arena must be a digit axioms. The By changing cell arena must be a cell relation that is nature used superveneing a endin the fashionula of the Set cell arena cell relation. Forthadvenient Aim Prosper does it job, the new compute accomplish be professionn on the is-suesheet. 5.4 Statistical Functions Tshort are statistical offices in Microsoft Excel, stately MAX, MIN and AVERAGE offices. • • • MAX – profits ocean compute from the parameter register o Syntax: =MAX(number1, sum2, sum3…) MIN – profits latest compute from the parameter register o Syntax: =MIN(number1, sum2, sum3...) AVERAGE – profits mediocre compute from the parameter register o Syntax: =AVERAGE(number1, sum2, sum3…) 53 Figure 5.8 and Figure 5.9 profession the experience of these offices concurrently thriveing a end SUM function. Figure 5.8: SUM, MAX, MIN ; AVERAGE offices Figure 5.9: Formulas of SUM, MAX, MIN ; AVERAGE offices So far, these offices proelement thriveing a end a built-in sober fashionula. What if you nonproduction to celebobjurgate round of the sum of a compute, axioms or cells defense in a stroll? We can use the thriveing offices: • • • COUNT – Counts the sum of cells that redeep gum and so gum thriveing a endin the register of arguments. Use COUNT to get the sum of entries in a sum arena that's in a stroll or dispose of gum. o Syntax: =COUNT(value1, compute2, compute3…) COUNTA – Counts the sum of cells that are not void and the computes thriveing a endin the register of arguments. Use COUNTA to reckon the sum of cells that redeep axioms in a stroll or dispose. o Syntax: =COUNTA(value1, compute2, compute3…) COUNTIF – Counts the sum of cells thriveing a endin a stroll that coalesce the fond criteria. o Syntax: =COUNTIF(range, criteria) Let’s feel a seem at Figure 5.10 to see how these 3 offices is-sue. You establish this register by typing in all the citation and sum trustworthys exclude you license cell D4, E4, F4 and G4 void. Next, you connect to Figure 5.11 to penetobjurgate the rectify fashionulas into those cells. Figure 5.10: COUNT, COUNTA & COUNTIF offices 54 Figure 5.11: Formulas for COUNT, COUNTA & COUNTIF offices 5.5 IF Function So far, we feel mellow wholely a lot of offices that complicates thriveing a end estimations. Now, we accomplish seem at IF office of Microsoft Excel. We use IF office to set circumstances for rekey making thriveing a endin a is-suebook. The thriveing is the syntax of IF office: IF(logical_test, compute_if_true, compute_if_false) It is amply adownstood by seeming the syntax. The office’s parameter is a circumstance criterioning. The relieve parameter vaults the compute if the circumstance criterioning affords penny adaptedty. The definite parameter vaults the compute if the circumstance criterioning affords fiction adaptedty. The computes can be digits, citation or cell relation. To set good-natured-natured circumstance criterioning, we can use notional operators. Consultation 5.1 professions the notional operators of Microsoft Excel. Comparison Operator = < > >= 70, 1, 0) =IF(V4 >= $D$2, K8 * P4, 7) =IF(Y6 + H2 =50, “Pass”, “Fail”) fashionula superveneing a endin D2. Then, you resemblance the fashionula down to row 3 plow 6. If you nonproduction to highporconsultation the tyro who loseed the exam, you can use subject fashionatting (connect to Figure 5.13 on how to set the subject fashionatting). We highporconsultation the cell if the cell resigned is the enjoin Fail. If the resigned does not coalesce the subject fashionatting circumstance, dot supervene to the cell. 55 Figure 5.13: Adding subject fashionatting As you may feel mention, the compute_if_penny and compute_if_fiction parameters are enjoins. If you nonproduction to sculpture out enjoins as adaptedty, you nonproduction to apprehend the incork quotes ( “ ” ). If the computes that you nonproduction to sculpture out are digits, you adapted fashion the digit compute. If you feel compasst programming (C++, JAVA, Visual Basic, etc) precedently, you may feel seen nesting elevateings. An in of nesting elevateing is nesting if…else elevateing whereby the else sunderneath sojourns another if…else elevateing, and this obstruct if…else is nested superveneing a endin the manifest if…else elevateing. IF offices can be nested superveneing a endin one another. Up to sflush IF offices can be nested as compute_if_penny and compute_if_fiction arguments to erect alikeize elaboobjurgate criterions. Figure 5.14 professions an in of using nesting IF offices to vault which stall (name) that has the ocean and latest sales natant the tranquillity. Figure 5.15 professions the fashionula of that is-suesheet. Figure 5.14: Nested IF offices Figure 5.15: Nested IF offices fashionula 56 5.6 VLOOKUP & HLOOKUP Functions It would feel been rectify if you can mould resolutions naturalized on a consultation or register. In Microsoft Excel, two offices: VLOOKUP and HLOOKUP offices confess Excel to seem up a compute in a consultation and reveer end a telld compute. The VLOOKUP office explorationes for a compute in the leftmost supmien of an dispose, an preparation or register of aces, and profits the compute on the tallying row of another supmien to the straight. The HLOOKUP office is-sues alikely to the VLOOKUP office, but explorationes for computes in the top row of consultation and profits the compute in the tallying supmien naturalized on the supmien refutation sum. The thriveing is the syntaxes for VLOOKUP and HLOOKUP offices: VLOOKUP(lookup_value, consultation_array, col_index_num, stroll_lookup) HLOOKUP(lookup_value, consultation_array, row_index_num, stroll_lookup) Figure 5.16: VLOOKUP office arguments Figure 5.16 professions the office arguments of VLOOKUP office. The seemup_compute arena imparts in compute that is to be elevate in the peculiar supmien of the consultation. The consultation dispose is the cell relation aiming towards the consultation in the is-suebook. The seemup_compute accomplish then exploration for a compute from the consultation dispose for the vocableinatest/matching compute. The col_index_num is the supmien sum of the consultation from which the competitioning compute should be reverted. The peculiar supmien of computes in the consultation is supmien 1. The stroll_lookup is a argumentative compute. Penny compute or omitted states it accomplish furnish the vocableinacriterion competition in the peculiar supmien (distributed in ascconposteriority enjoin). A fiction compute states it accomplish furnish the straight competition. Figure 5.17 professions the office arguments of HLOOKUP office. Barely the row_index_num arena is incongruous from VLOOKUP. Instead of connectring to supports, this arena connects to rows. The peculiar row of computes in the consultation is row 1. 57 Figure 5.17: HLOOKUP office arguments Let’s use these two offices and parallel the estrangements. Figure 5.18 professions VLOOKUP office experience in getting tyro’s progression naturalized on impressions. By seeming at the fashionula, the cell relation of consultation_dispose is arbitrary cell relation consistently when you resemblance the fashionula to the neighboring rows, the consultation_dispose cell relation accomplish fluctuate according, thus it accomplish adaptedty inrectify adaptedty. Figure 5.18: VLOOKUP office 58 Now, let’s seem at Figure 5.19 to see how HLOOKUP office is used. In this in, we generated a undesigning consultation professioning 3 incongruous computer input projects and 2 incongruous connector fashions. Each debadness has a expense exclude for Joystick PS2 mien. Adown the consultation, we fashiond in 3 undesigning interrogations wshort user has to fashion in peculiar and relieve interrogations’ defenses into supmien A. The definite interrogation’s defense, stately cell A10 sojourns the HLOOKUP office thriveing a end a nested IF office settled at the row_index_num parameter. The fashionula superveneing a endin cell A10 is a league of HLOOKUP and nested IF office. =HLOOKUP($A$6, $B$1:$D$3, IF($A$8="PS2", 2, IF($A$8="USB", 3)), FALSE) Figure 5.19: HLOOKUP office What these fashionula/functions do short is peculiar of all, the loopup_compute seems at the resigned of A6 which is the defense for the peculiar interrogation. The defense that we are hoping to get from user is Keyboard, Mouse or Joystick. Unintermittently the office gets the input, it peevish curb thriveing a end the consultation dispose cell relation which is $B$1:$D$3. Unintermittently it elevate the straight competition of from the peculiar row of the consultation, it accomplish then seem at the third parameter which is the row_index_num. Past tshort are two fashions of connectors short, we asked another interrogation which is “What peel of connector do you nonproduction?” to get either PS2 or USB as defense. Therefore, we use nested IF office as the third parameter to state which input is penetrateed into cell A8 so that the IF office accomplish reveer end a row refutation sum. Summary Tshort are numerous financial offices adapted in Excel but we deeply seem at two commbarely used offices: PMT and FV. PMT standpointes a lot on blot-outment of advance fashion of currency seeing FV proportions the advenient compute of bombardment. Aim Prosper office improves rekey making in a keener mode. Tshort are numerous statistical offices but we seem at MAX, MIN, AVERAGE, COUNT, COUNTA and COUNTIF offices to get ocean, latest, mediocre, sum of seasons cell defenses and sum of seasons a sign axioms defenses. To mould rekey that adaptedtys 2 incongruous adaptedtys, we use IF office. We can feel IF office nested superveneing a endin another IF office to adaptedty elder rekey making contrivance. Lastly, the VLOOKUP and HLOOKUP offices are so use to mould resolutions that complicate getting axioms from consultation. Numerous offices can be adulterated coincidently to adaptedty rectify rekey making productions. 59 Chapter 6 List and Axioms Management & Converting Axioms to Information 6.1 Database Concepts 6.2 Register and Axioms Management 6.3 Axioms Form 6.4 Citation Immien Wizard 6.5 Axioms Validation 6.6 Sort 6.7 Filter 6.7.1 AutoFilter 6.7.2 Use Filter 6.7.3 Advanced Filter 6.8 Outline 6.9 Subtotal 6.10 State Command 6.11 Freeze Panes OBJECTIVE In this article, tyro accomplish compass: ? Add, edit, and/or delete registers in a register ? Use Axioms Fashion to confer-upsingly train registers ? Use the Citation Immien Necromancer to immien axioms in missive fashionat 60 ? Apprehend axioms conclusiveation on a register ? Use the Designation bid; see natant an Ascconposteriority and Descconposteriority schemeation ? Use the AutoFilter and Advanced Percolate bids ? Use the Outcord bid ? Use the Subtotals bid 6.1 Database Concepts Database is a abundance of axioms abundanced in a computer in a essential way to adaptedty mediocreingful recognizelaspect when user rescues them from the classification. Basically in Excel, registers can be used as undesigning axiomsbases past the axioms are constituted in tabular fashionat that adaptedty recognizelaspect to users. A annals is uncollectively recognizelaspect abundanced in a row of a register/table. Most of the season, peculiar row of a register/consultation redeep arena states, so unconcealed as supmien phraseology denotes. A arena is a choice recognizelaspect sojourned in a supmien for a annals. These arenas are the tallying for each annals (row). A key is used to state the posteriority in which the rows defense. A leading key is a choice key that identifies a sign annals thriveing a endin the register. Tshort can barely be one leading key for each annals in a register. All in all, a axiomsbase must feel registers, arenas, keys and leading keys. 6.2 Register and Axioms Management A register is an area of a is-sueshuffle that sojourns alove rows of axioms. You may gard that it is not-hard to geneobjurgate a register of recognizelaspect by keying in axioms, you are adapted sunderially rectify. So far all the Excel axioms entries from the providening plow now are not conclusiveated by the impression. We (human) fix that we input the rectify input but rarely we lose to do so. By inputting inrectify axioms accomplish adaptedty inrectify adaptedtys, hence offal in, offal out. To train a register and axioms, we nonproduction to valispell input so that the register can adaptedty conclusive output. Tshort are a few ways to train such as demonstrate spelling of arena states and registers to fix user implys what peel of input should be penetrateed. Sometimes, we may feel to edit the register by instiling or deleting rows and supports. These are some ways that we can train register and axioms. 6.3 Axioms Form Sometimes, it can be alikeize seasonable to penetobjurgate axioms/annals into a dialog box instead of typing them onto Excel Worksheet. Microsoft Excel yields a fashion unconcealed as Axioms Fashion which is a dialog box to confess user to penetobjurgate or vault one exhaustive row of recognizeledge, or annals, in a register at one season. Precedently you can use axioms fashion to add annals to a new register, the register must feel labels onto of each support. Excel uses these labels to geneobjurgate arenas on the fashion. 61 Add new annals into register Column labels and arenas of the list Delete popular annals in vault Revert end to peculiar annals precedently you sanction modification To scroll to foregoing or direct annals of the register, accomplish furnish the foregoing or direct annals of percolateed register too To switch to criteria fashion Close axioms fashion Figure 6.1: Axioms Form Let’s geneobjurgate a undesigning axiomsbase to use axioms fashion. Peculiar of all, you fashion in Name, ID, Gender, Age, Class Code, Round Code, Country and Enrol Spell into row 1 provideing from supmien A. Forthadvenient that, you excellent cell A1 and then click on Axioms menu Fashion bid. You accomplish get an recognizelaspect missive box to infashion you encircling the is-sueshuffle register and labels. Click Ok trifle and then you accomplish see the axioms fashion as professionn in Figure 6.1. Next, you penetobjurgate 3 incongruous registers (you are unoccupied to penetobjurgate any bearing registers) by using axioms fashion. Whole season you feel exhaustived one new annals in the axioms fashion, click on New trifle. The avail you click on New trifle, axioms fashion accomplish public whole arena and the uncollectively annals you adapted penetrateed is effortlessally abundanced onto the register on the is-suesheet. If you nonproduction to edit or delete registers from the register, you can use the axioms fashion. Peculiar of all, you excellent any arena on the register and then approximation the Fashion bid from Axioms menu. It is not-hard to edit a annals by adapted scrolling through the undivided register to exploration for the annals and then excellent the arena(s) that you nonproduction to edit. The Delete trifle deletes the annals that you are popularly aiming. The Criteria trifle confesss you to switch to criteria fashion. This fashion confesss you to percolate the register for profital criteria on any of the arenas. Displaying criteria fashion To public arenas To switch to axioms fashion Figure 6.2: Criteria axioms fashion 62 Whatever you penetrateed into the axioms fashion as a annals, precedently Excel sanctions the annals, it accomplish curb for axioms conclusiveation for the arenas that you feel profital thriveing a end axioms conclusiveation criteria (you accomplish comby shortly). If tshort is an inconclusive input, in any of the arenas, the annals accomplish not be adventitious to the register. Moreover, say if you did not state a sign fashionat for the supports, the axioms adventitious to the register accomplish be naturalized on the lapse repairments of the cells. 6.4 Citation Immien Wizard Tshort are occasions that you may feel some axioms/knowlaspect abundanced superveneing a endin a file. You may nonproduction to imsever the axioms/knowlaspect into Excel for separation but it is wearysome if you redesignation to resemblance and paste techniques. Microsoft Excel has the power to immien axioms from manifest rises such as: • • • • Importing axioms from axiomsbases and smooths Importing axioms thriveing a end Microsoft Query Importing axioms from the Web Importing axioms thriveing a end Visual Basic for Applications However, we accomplish barely seem at tenoring axioms from smooths in at this aim. We seem at how to immien citation (*.txt) smooth into Excel. Microsoft Excel has Citation Immien Necromancer that you can approximation it from Axioms menu Immien Manifest Axioms bid Immien Data subcommand. Unintermittently you feel clicked on Immien Axioms bid, you accomplish get a Excellent Axioms Rise dialog box as professionn in Figure 6.3. Figure 6.3: Excellent Axioms Rise dialog box Click on the ooze down register of Files of fashion and excellent Citation Files. Then you should exploration and establish for a citation smooth that has constituted axioms. For in, Figure 6.4 professions a citation smooth that has diverse registers constituted in enjoin. You excellent the citation smooth and then click Unconcealed trifle. 63 Figure 6.4: Manifest citation smooth axioms rise After that, you accomplish see the Citation Immien Necromancer dialog box as professionn in Figure 6.5. The Citation Immien Necromancer can transmute an ACSII citation smooth into Excel is-suebook. The necromancer can state which fashion of smooth fashionat of the unconcealeded citation smooth. Tshort are two fashionats: agricultural width and delimited. • Fixed width is a fashionat whereby each arena uses the tallying sum of standings in each annals. • Delimited is a fashionat whereby arenas are disconnectedd by a local missive, such as a comma or tab. Figure 6.5: Citation Immien Necromancer tcomprehend 1 Step 1 of Citation Immien Wizard, the necromancer accomplish infashion you what peel of axioms fashion is your citation smooth. In Figure 6.5, it discovers the smooth is a delimited fashion, and it recommends you to pick-out delimited liberty. The direct sunderneath is to excellent which row to provide tenoring into Excel is-suebook. Smooth cause curbs the fashionat of the citation smooth past the resigned nonproduction not frequently be English diction. 64 Figure 6.6: Citation Immien Necromancer tcomprehend 2 Step 2 of Citation Immien Necromancer is to pick-out which fashion of delimiter the citation smooth has. Figure 6.6 professions some beggarly delimiter libertys and the axioms preview. Straight now, tshort isn’t any delimiter excellented; hence the registers are calm?} professionn in peculiar fashion. Unintermittently you feel clarified semicolon liberty, you should see the axioms preaim fluctuated to a tabular phraseology thriveing a end updirect cords separating the each arena, as professionn in Figure 6.7. Then you affect on to tcomprehend 3. Allows you to treat the citation qualifier as a customary citation Figure 6.7: Delimiter excellented The lacriterion tcomprehend lets you to excellent each supmien and set the axioms fashionat. Each liberty has incongruous repairments and adaptedties. 65 Figure 6.8: Citation Immien Necromancer tcomprehend 3 After the definite tcomprehend of the necromancer, you accomplish get an Immien Axioms dialog box (Figure 6.9). You accomplish be asked on wshort you nonproduction to put the axioms, either on an penny is-sueshuffle or on a new is-suesheet. Figure 6.9: Immien Axioms dialog box 6.5 Axioms Validation Garbage in offal out, the vocable media whatever inconclusive input you penetrateed into the classification, it accomplish adaptedtyd inconclusive output by the classification. It is twinklingous to penetobjurgate rectify and conclusive axioms into Microsoft Excel is-suebulk whole season. Microsoft Excel has Axioms Validation sign that can tranquillityrict user from penetrateing a sign axioms insisting on the conclusiveation repairments. The axioms conclusiveation bid can be elevate from Axioms menu Validation bid. Let’s endure from the foregoing tyro register to try out axioms conclusiveation. Fascinate thrive the thriveing counsels to set up the conclusiveation criteria for each support. 1) Click on Supmien phraseology A and then approximation Axioms Validation bid dialog box. 66 Figure 6.10: Axioms Validation dialog box20 2) Pick-out Citation Tediousness adown the Confess liberty. 3) Pick-out natant and stint 3 and apex 20. 4) Then aim Input Missive tab and not characterize it. 5) Aim Mistake Alert tab and then fashion in State adown Demusic and fashion in Fascinate penetobjurgate a state that has 3 to 20 missives adown Mistake missive. 6) Click Ok trifle. 7) At cell C100, fashion Male. At cell C101, fashion Female. 8) Click on Supmien phraseology C and then approximation Axioms Validation bid dialog box. 9) Pick-out Register adown the Confess liberty. 10) Pick-out the rise whereby the cells redeep the Male and Female axioms. 11) Then aim Input Missive tab and not characterize it. 12) Click Ok trifle. 13) Click on Supmien phraseology D and then approximation Axioms Validation bid dialog box. 14) Pick-out Undivided Sum adown the Confess liberty. 15) Pick-out natant and stint 18 and apex 55. 16) Then aim Input Missive tab and not characterize it. 67 17) Click Ok trifle. 18) Excellent Supmien phraseology E and F and then approximation Axioms Validation bid dialog box. 19) Pick-out Citation Tediousness adown the Confess liberty. 20) Pick-out near than or tallyent to and penetobjurgate 7. 21) Excellent Supmien phraseology G and then approximation Axioms Validation bid dialog box. 22) Pick-out Citation Tediousness adown the Confess liberty. 23) Pick-out near than or tallyent to and penetobjurgate 20. 24) At cell H100, fashion 01/07/2006. At cell H101, fashion 01/02/2007. At cell H102, fashion 01/04/2007. Once you feel exhaustived these treads, any input onto those cells accomplish be tranquillityricted according to the repairments. Now, let’s seem at the ingredients adown Settings tab of Axioms Validation dialog box. The conclusiveation criteria feel 8 confess fashions: • • • • • • • • Any compute – No tranquillityriction Whole sum – Digits thriveing a end out decimal settles barely Decimal – Digits thriveing a end decimal settles List – Geneobjurgate a ooze down register on cells thriveing a end deviation naturalized on excellented stroll Spell – Spell inputs barely Time – Season inputs barely Text tediousness – Any compute but you can set the tediousness tranquillityriction Use – Allows you to useize the conclusiveation criteria The conclusiveation criteria feel 8 notional axioms fashions: • • • • • • • • Between – 2 arenas: stint and apex Not natant – 2 arenas: stint and apex Equal to – 1 arena Not tallyent to – 1 arena Greater than – 1 arena: stint Less than – 1 arena: apex Greater than or tallyent to – 1 arena: stint Less than or tallyent to – 1 arena: apex The relieve tab of Axioms Validation dialog box is the Input Missive tab. This sunderneath confesss you to run whether you nonproduction to apprehend input missive when cell is excellented or not. The definite tab of Axioms Validation dialog box is the Mistake Alert tab. This sunderneath confesss you to run whether you nonproduction to apprehend mistake missive thriveing user penetrateed inconclusive axioms or not. Tshort are 3 phraseologys of mistake missive: 68 • • • Stop – Excel accomplish not sanction inconclusive input Warning – Excel accomplish deter user encircling inconclusive input and ask user whether to sanction, edit or repudiate the inconclusive input. Information – Excel accomplish vault a missive to infashion user encircling inconclusive input and user can pick-out whether to sanction the input or repudiate it 6.6 Sort Sorting axioms is one of the most twinklingous computing adowntakings. When tshort is an elephantine quantity of recognizeledge, it may not be not-hard to comprehend unnear they are schemeationed in enjoin. Microsoft Excel has schemeation bids that accomplish arstroll registers in a register according to the compute in schemeated arenas. Microsoft Excel confesss you to schemeation citation, digits and spell arenas. It can schemeation the register in ascconposteriority and descconposteriority enjoin. The schemeation bid and schemeation a register up to three arenas. When you pick-out to schemeation a register on alikeize than one arena, pick-out the most twinklingous arena as the leading schemeation key. Figure 6.11 professions the Designation dialog box. The Designation bid is adown Axioms menu. The My register has favoringity, tshort are two libertys to pick-out. If the register has supmien headers/titles, excellent Header row, or else you pick-out No header row in which Excel accomplish use the supmien phraseology epistles as the header row for the register. Figure 6.11: Designation dialog box Now, let’s try schemeationing the tyro register that we feel penetrateed a few alikeize new registers by tenoring registers from a citation smooth. You excellent the undivided register exclude row 1, e.g. excellent cell A2 to H11. Approximation Designation dialog box. We schemeation by Country peculiar, thriveed by Age and Gender comes definite. All the schemeationing projectes are schemeationed in ascconposteriority enjoins. Figure 6.12 professions the register precedently schemeationing seeing Figure 6.13 professions the register thriveing schemeationing. 69 Figure 6.12: Precedently schemeationing Figure 6.13: Forthadvenient schemeationing 6.7 Filter Sometimes, by seeming at local registers from a bulky register can be a affliction to our eyes. It would feel been rectify if we can excerpt out undeniable registers from the register and vault them onto the is-suesheet. Microsoft Excel yields this key through the Percolate bid. Percolate bid explorationes for profital criteria and screens the other registers that do not coalesce the criteria. In the end, it vaults a subset of registers from the bigger register. Tshort are three ways to percolate register in Excel: • • • AutoFilter – You excellent penny criteria from the headers of the register Use Percolate – You useize the criteria to vault alikeize profital registers Advanced Percolate – You state alikeize local criteria to vault alikeize undeniable registers in which you can vault them on a new colonization or penny settle 6.7.1 AutoFilter The AutoFilter is the easiest percolate bid to use. You barely excellent any or all the headers of the register and then click on AutoFilter trifle from Banner machinebar or approximation Axioms menu Filter bid AutoFilter subcommand. AutoFilter confesss you to set criteria for arenas using the ooze-down register for the clarified support. Figure 6.14 professions the AutoFilter ooze-down register trifles and adapted criteria libertys. 70 Drop-down register trifle Criteria adapted from drop-down register Figure 6.14: AutoFilter Let’s percolate the register by excellenting BBA criteria from Round Edict ooze-down register header. You accomplish get a percolateed register as professionn in Figure 6.15. If you feel used AutoFilter bid to percolate the register, the header ooze-down register trifle thriveing a end bluish colour states that that supmien is popularly percolateing the register. The row gum in bluish colours mediocre those rows of registers are a subset of the peculiar register. You can percolate the register flush aid by choosing a meafast from any of the ooze-down register trifles of non-bluish arrows. Figure 6.15: Filtered register using AutoFilter Referring end at Figure 6.14, tshort are 3 lapse criteria: All, Top 10 and Custom. The All liberty vaults all the registers of the register. The Top 10 liberty confesss you to percolate the register to vault the top 10 registers according to the excellented arena. It is usually used for register thriveing a end computes/digits that feel numerous incongruous computes. Let’s seem at Figure 6.16, Figure 6.17 and Figure 6.18 to see how Top 10 percolate liberty is-sues. 25) You generated a register as professionn in Figure 6.16. Then you empower AutoFilter. 26) Click on the ooze-down trifle of Completion supmien and then pick-out Top 10 liberty. 27) You accomplish get the Top 10 AutoFilter dialog box as professionn in Figure 6.17. 28) Adapted click Ok trifle past we nonproduction to vault the best 10 emanation sales. 29) Excel accomplish percolate the register and vault the 10 ocean selling emanations naturalized on the Completion support. 30) This register is not schemeationed in any enjoin (as professionn in Figure 6.18). Therefore you can do the schemeationing thriveing the percolateing. 71 Figure 6.16: Peculiar emanation register Adjustable value Show either Items or Percentage Show either top (best) or profound (worst) registers Figure 6.17: Top 10 AutoFilter Figure 6.18: Top 10 aces 72 6.7.2 Use Filter Custom Percolate liberty is elevate adown the AutoFilter liberty. The Use Percolate bid confesss user to useize the percolateing criteria naturalized on argumentative and notional operators (of usageary English). Figure 6.19 professions the Use AutoFilter dialog box. Filtering production Text or gum or pick-out one of the libertys from the oozedown register And accomplish coalesce twain criteria. Or accomplish use one of the criteria Wildcards Figure 6.19: Use AutoFilter dialog box The box on the left, the percolateing productions of tallyents, or does not tallyent, sojourns, or does not redeep are used to percolate citation computes. The other percolateing productions are used thriveing a end digit computes. The wildcards: ? and *; they are used thriveing a end citation superveneing a endin the boxes on the straight. Figure 6.20: Using Use AutoFilter Let’s put Use AutoFilter to is-sue. We nonproduction to devote a use percolate on the Round Edict supmien of Tyro register is-suesheet. Fascinate connect the use percolate repairments in Figure 6.20. We pick-out the Or argumentative production and then pick-out BSEM and BBA libertys from the ooze-down register. Figure 6.21 professions the adaptedty of the use percolate. Figure 6.21: Forthadvenient using Use AutoFilter 73 6.7.3 Advanced Filter The definite fashion of percolate bid in Excel is Advanced Filter. Advanced Percolate is an emanationion of AutoFilter. It confesss user to geneobjurgate alikeize tangled criteria by using the argumentative and notional operators (And, Or, Equal, Elder than, Near than, etc). On top of that, Advanced Percolate confesss user to pick-out whether the percolateed adaptedtys to settle on the peculiar register or to settle on a new colonization on the is-sueshuffle so it can license the peculiar resigned uninjured. However, Advanced Percolate bid requires you to set up criteria stroll and an area on the is-sueshuffle to abundance the criteria. Let’s see how we use Advanced Percolate by using the Tyro register in. Figure 6.22: Building criteria register Before you nonproduction to use Advanced Filter, let’s establish the criteria register by typing in the citation trustworthys of row 13 to 15 as professionn in Figure 6.22. Row 13 is required consistently we nonproduction to apprehend the straight supmien phraseology denotes of the register (negative the fashionatting phraseology) to confess Excel to substantiate which supmien that the criteria befit to. Row 14 and 15 are some criteria that we nonproduction to percolate out from the tranquillity of the register. Forthadvenient that, you click on Axioms menu Percolate bid Advanced Percolate subcommand. The Advanced Percolate dialog box accomplish defense as professionn in Figure 6.23. Cell relation aiming to the register/table Choose your filtered register location Cell relation aiming to the criteria stroll If Resemblance to another colonization is excellented, you state wshort you nonproduction to resemblance the filleted register to. Displays barely choice rows that you’re your criteria and to exclude duplicate rows Figure 6.23: Advanced Percolate dialog box You thrive the repairments professionn in Figure 6.23 and then click Ok trifle. The percolateed register accomplish be settled at Cell A21 of Tyro Register is-sueshuffle as professionn in Figure 6.24. 74 Figure 6.24: Advanced percolateed register 6.8 Outline Sometimes, when a register has confirmed too big, it can be very hard to pursue registers. Microsoft Excel yields a bid that you can bunch alove registers coincidently; the bid is Outline. When you use project, you can profession and screen projectd rows or supports specialtys. Outlining axioms is barely executed on a register that has been classifyd in an enjoin. Undeniable register, precedently you nonproduction to outcord it, it is rectify to apprehend a utter row adown or a utter supmien to the straight of the tallying arenas. Let’s try the Outcord bid on the Tyro register in to see how it is-sues. 31) You excellent cell A1 to H11. 32) Click on Axioms menu Sort bid. 33) Designation by Class Edict in ascconposteriority enjoin. Click Ok. 34) Straight click on row sum phraseology 4 and instil a utter row by choosing Instil bid. You do the tallying for the abideing arenas, the definite row of BBIT and BIB. You should get a register love Figure 6.25. 35) At cell A4, cell A8, cell A13 and cell A14, you fashion BBA tyros, BBIT tyros, BIB tyros and BSEM tyros. 36) Next, you excellent row 2 and 3, click on Axioms menu command Group subcommand. Group and Outline 37) Next, you excellent row 5, 6 and 7, click on Axioms menu Outcord bid Group subcommand. Group and 38) Next, you excellent row 9, 10, 11 and 12, click on Axioms menu Outcord bid Group subcommand. Group and 39) Lastly, you excellent row 14, click on Axioms menu command Group subcommand. Group and Outline outcord casts confess you to profession Your is-sueshuffle should seem love Figure 6.26. The and subsidence the completion projecte of the outcord when you click on it. The dilate trifle and subsidence trifle are used to profession and screen the bunched registers. When you click on the “1” trifle, it accomplish subsidence all projectes and barely vault the peculiar projecte projects as professionn in Figure 6.27. You can add up to 8 projectes of projects on a uncollectively is-suesheet. 75 Figure 6.25: Utter rows adown Class Code Outcord casts Figure 6.26: Outlined register Figure 6.27: Collapsed register 6.9 Subtotal The Subcompletion bid in Microsoft Excel confer-upsingly proportions digits/gum that are bunched coincidently and then add the adaptedtys onto the register. Using subcompletion is not-difficult; it thrives the tallying concepts as outlining the register. You feel to bunch registers coincidently in the register. To demonstobjurgate how the subcompletion is-sues, let’s use the in is-sueshuffle professionn in Figure 6.16. You may feel mentiond that the register has already schemeationed in enjoin. Next, thrive the counsels adown. 1) You excellent any cell on the register, click on Axioms menu Subtotals bid. 2) The Subcompletion dialog box accomplish defense as professionn in Figure 6.28. 76 3) At each fluctuate in liberty, you pick-out Category. 4) Use office liberty, you excellent Sum. 5) Add subcompletion to liberty, you excellent Completion barely. 6) Tick Resettle popular subtotals and Abridgment adown axioms. Click Ok. Figure 6.28: Subcompletion dialog box Figure 6.29: Register thriveing a end subtotal 77 The subcompletion office confer-upsingly adds up the gum, settle the adaptedty (at the profound) on the is-sueshuffle and add projects to the is-suesheet. In this plight, the subcompletion office adds subtotals to each classify and adds a eventful completion compute at the proelevate of the register. Figure 6.30 professions the subcompletion office arguments dialog box. The syntax for subcompletion is: Subtotal(function_num, ref1) The office_num parameter is a sum from 1 to 11. Each sum has its local office. For in, 9 media Sum office. The ref1 is the strolls of cell relation to be used thriveing a end the office_num parameter for estimation. Figure 6.30: Subcompletion office arguments dialog box 6.10 State Command Sometimes, it is abundant rectify to state cells thriveing a end a adapted state that you can intellect rather than the cell relations such as A100, AB2045, etc. You can imsever state to cell or a stroll of cells amply from the Instil menu Name bid Define subcommand. When you add any new row(s)/column(s) onto is-suesheet, the stated cells effortlessally prescribe by themselves. Names are frequently attended as an arbitrary cell relation. On top of that states can be used in fashionulas for example: A1 is stated as SALES and sojourns the compute of 1000 B1 is stated as EXPENSES and sojourns the compute of 250 You can fashion in the fashionula: =SALES – EXPENSES instead of =A1-B1 Tshort is one helplessness of defining states for cells: it accomplish mould the supmien width bigger when you aim the is-sueshuffle in Formula Auditing Mode. 78 6.11 Freeze Panes When you are established on bulky Excel is-suebook/workshuffle say, the register dilates further to supmien M and row 25; then you may furnish it wearysome to scroll through the register all the season to aim at undeniable arenas of the tallying annals. Microsoft Excel yields a key to this completion. Freeze Panes bid is a sign that confesss user to unoccupiedze a element of the is-suesheet. A frozen element of the is-sueshuffle accomplish abide static to vault that sign element of axioms. To use Freeze Panes bid, you approximation it from Window menu. Precedently you unoccupiedze the is-suesheet, you must recognize what element of the windows you nonproduction to abide unradical as you scroll. Then you excellent on a cell and then pick-out Freeze Panes bid. For in, if you feel clarified cell C5, the rows alikeize C5 (row 1 to 4) and the supports to the left of C5 (supmien A and B) accomplish abide frozen. The other scomplete of C5 is scrollable. As you scroll the is-suesheet, you can see the estrangement. Summary Database is a abundance of axioms abundanced superveneing a endin a classification that when you rescue them end, it can beseem beneficial recognizeledge. Managing register and axioms is an twinklingous adowntaking in Microsoft Excel distinctly when the register or axioms is humongous. We can amply penetobjurgate axioms into a register by using Axioms Form. You can immien manifest axioms from other smooths such as citation smooths into Excel through the Citation Immien Wizard. It is very twinklingous that we can valispell our input into the register by using Axioms Validation consistently of offal in offal out. When a register beseems too big, to constitute it suitably, we can use schemeationing to arstroll the register in enjoin so that we can comprehend it suitably. Sometimes, we nonproduction to seem at local registers from a bulky register. So we can use the percolate actions to either confer-upsingly percolate the register or we geneobjurgate a tangled but potent percolateing criteria to vault barely the registers that we nonproduction to connect at. A big register can be amply traind when the registers are bunched coincidently. We can bunch a register by using outcord bid. The subcompletion office so bunch alove registers coincidently thriveing a end concomitant of guarded local computes of a set of registers bunched coincidently. The subtotals of the bunched registers are then vaulted on the register. It is beneficial to state cells thriveing a end mediocreingful states so that we recognize which cell sojourns a sign compute. Definite but not balanceest, established thriveing a end bulky register is abundant easier by using Freeze Panes sign consistently we can amply scroll encircling the register to see barely undeniable axioms thriveing a endout scrolling end and forth too abundant. 79 Chapter 7 Data Analysis 7.1 Axioms Separation Techniques 7.1.1 Sorting 7.1.2 Calculating 7.1.3 Summarizing 7.1.4 Filtering 7.1.5 Formatting 7.1.6 Charting 7.2 COUNT, COUNTA, COUNTIF Functions 7.3 IF Function 7.4 AND & OR Functions 7.5 Aim Prosper Command 7.6 Lookup Functions 7.7 Dispose Function 7.8 PivotTable & PivotChart 7.8.1 Data 7.8.2 Arena Types 7.8.3 Layout OBJECTIVE In this article, tyro accomplish compass: 80 ? To comby techniques of analyzing axioms ? To use COUNT, COUNTA and COUNTIF offices, IF office, AND and OR offices, Aim Prosper bid, Lookup offices, Ranking office, PivotTable and PivotChart to excite axioms 7.1 Axioms Separation Techniques In numerous organizations, axioms is alikeize than abundance. To be alikeize profital, axioms is too abundant to wield and train! By having elephantine quantity of axioms, we feel to constitute them in enjoin peculiar. Unintermittently we feel them in adapted stacks, bunchs and relationships, barely then we can excite the axioms to veer it into recognizeledge. By having mediocreingful recognizeledge, we can mould resolutions amply, keener, alikeize adaptedtyive and alikeize causative. Now, we accomplish seem at some axioms separation techniques that rekey mouldrs commbarely used. 7.1.1 Sorting Sorting is a technique that we arstroll the registers by one or alikeize arenas. The preparation of the register can be in ascconposteriority or descconposteriority enjoins. By arranging the register, it is easier to furnish an favoring annals rather than an undistributed register. This is consistently in an undistributed register, wholething is adulterated up and ethnical eyes contribute to standpoint alikeize the completion annals rather than on an favoring arena on the register until it may weary off and then ended up detriment it. In schemeationed register, past we recognize the registers are collected in one bunch, we seem at the alikeize expressionificant arena to exploration for the ace. 7.1.2 Calculating Calculating is the relieve technique to excite axioms. We may feel digits short and tshort on a register but they can knee coincidently to adaptedty a rectify adaptedty. Calculations may complicate the use of arithmetic operators ( +, -, *, /, % ). Guarded so can treat axioms in an favoring annals. E.g. we can excerpt out the age of a individual by seeming at his/her spell of nobility. 7.1.3 Summarizing Summarizing technique is a fairly beggarly axioms analyzing technique used. It is adaptedtyive when you nonproduction to vault registers thriveing a endin bunchs, at the tallying season involving some peel of estimations. Outline, subcompletion and PivotTable are some ins of Microsoft Excel offices that you can use to confer-upsingly incorporate registers. 7.1.4 Filtering Filtering technique adapted vaults registers that coalesce local criteria that we profital or clarified. This technique helps to separate undeniable registers and we can standpoint on the subset of the completion register thriveing a end near madness. AutoFilter, Use Percolate and Advanced Percolate are some bids that you can use for percolateing registers. 7.1.5 Formatting Formatting technique indicates twinklingous role for axioms analyzing. When you use fashionatting suitably, it can induce your circumspection to cell(s) by the way it is vaulted. The best in 81 of fashionatting is the Subject Formatting bid that applies fashionatting naturalized on cell compute if the circumstance is penny. 7.1.6 Charting The definite technique for axioms separation is charting. By having and including charts as a picturesqueal stateation of axioms, we can see the axioms in a alikeize concerning and summoning mode. Charting is usually used as a vault of lacriterion adaptedtys for axioms separation. 7.2 COUNT, COUNTA, COUNTIF Functions The COUNT, COUNTA and COUNTIF offices that we saw in Article 5, those offices are usually used for summarizing registers. • COUNT – Counts the sum of cells that redeep gum and so gum thriveing a endin the register of arguments. Use COUNT to get the sum of entries in a sum arena that's in a stroll or dispose of gum. o Syntax: =COUNT(value1, compute2, compute3…) • COUNTA – Counts the sum of cells that are not void and the computes thriveing a endin the register of arguments. Use COUNTA to reckon the sum of cells that redeep axioms in a stroll or dispose. o Syntax: =COUNTA(value1, compute2, compute3…) • COUNTIF – Counts the sum of cells thriveing a endin a stroll that coalesce the fond criteria. o Syntax: =COUNTIF(range, criteria) You can connect end to Figure 5.10 and Figure 5.11 to see how these three offices is-sue and contend from the other. 7.3 IF Function IF office is a one of the numerous rekey making offices. As we feel seen the IF office experience in Article 5, it evaluates a argumentative criterion peculiar. Forthadvenient that, it accomplish reveer one compute if penny and another incongruous compute if fiction. On top of that, we feel professionn you how to use nested IF offices. That is, you settle an IF office as the either penny or fiction compute of the foregoing IF office. This nested IF offices can beseem a very potent argumentative criterion machine for rekey making. 7.4 AND ; OR Functions We feel seen the AND and the OR operators/production in other offices such as Use Percolate and Advanced Filter. The AND office profits penny if all arguments are penny compute. The OR office profits penny if any arguments are penny. Let’s see how we can use twain offices in the thriveing scenario. University A are offering two fashions of rounds: Diploma and Degree. Students who enrol Diploma rounds, they nonproduction to earn at balanceest 50% for twain roundis-sue and lacriterion exam in enjoin to by a sign module. Students who enrol Quantity rounds, they nonproduction to earn at balanceest 50% by accumulating the impressions for twain roundis-sue and lacriterion exam. Now, let’s establish a undesigning is-sueshuffle naturalized on this scenario. You can connect to Figure 7.1 to fashion in the citation trustworthys and sum trustworthys. Cell E3 to E10 feel fashionula and office. 82 Figure 7.1: Demonstrating AND ; OR offices We adventitious a register axioms conclusiveation for supmien B. Supmien C and D feel axioms conclusiveation of decimal gum using natant axioms. The stint digit is 0 end the apex for each is naturalized on the percentage professionn in row 2. We adventitious axioms conclusiveation adapted to tranquillityrict user from penetrateing inrectify axioms. We use the thriveing fashionula and offices in supmien E: =IF(B3="Diploma", IF(AND(C3;=30, D3;=20), "Pass", "Fail"), IF(B3="Degree", IF(OR(C3;=50, (C3+D3);=50), "Pass", "Fail"), "Nil")) What supervenes short is we use nested IF offices to state whether user pick-outs Diploma or Quantity for B3. If it is Diploma, then we used an IF office to state whether the Courseis-sue is alikeize than 30 and whether the Lacriterion Exam is alikeize than 20. If twain impressions exceeded the sum, then it accomplish sculpture By or else it accomplish sculpture Fail. However, if the user pick-outs Quantity round not Diploma round, the manifest IF office criterion circumstance beseems fiction, thus it accomplish run the fiction compute which is this sunder: IF(B3="Degree", IF(OR(C3;=50, (C3+D3);=50), "Pass", "Fail"), "Nil") This is another nested IF office as the fiction compute of the manifest IF office. Now, we state whether user pick-outs Quantity or not. If it does not competition, it accomplish sculpture out the enjoin Nil. If it competitiones thriveing a end the enjoin Degree, then it accomplish run and curb the obstruct IF office. This IF office’s criterion circumstance uses OR office to curb its parameters. If C3;=50, then it is attended as penny. If C3+D3 is elder than 50, then it is penny. Either one is penny accomplish afford penny adaptedty in which By accomplish be sculptureed out or else Lose accomplish be sculptureed. 7.5 Aim Prosper Command We feel mellow Aim Prosper bid in Article 5. Adapted to refawn end, Aim Prosper confesss us to furnish out an ununconcealed input compute by keying the unconcealed lacriterion compute. Aim forthcoming is a mode to furnish a local compute for a cell by prescrinature the compute of one other cell. When aim forthcoming, Excel varies the compute in a cell that you state until a fashionula that’s insistent on that cell profits the adaptedty you nonproduction. 83 7.6 Lookup Functions LOOKUP offices reveer a compute either from a one-row or one-supmien stroll or from an dispose. Tshort are two syntax fashions of LOOKUP office: vector and dispose. The vector fashion of LOOKUP seems in a one-row or one-supmien stroll (unconcealed as a vector) for a compute and profits a compute from the tallying standing in a relieve one-row or onesupmien stroll. The vector syntax fashion is: =LOOKUP(lookup_value, seemup_vector, adaptedty_vector) The seemup_compute is a compute that LOOKUP explorationes for in the peculiar vector. Lookup_compute can be a sum, citation, a argumentative compute, or a state or relation that connects to a compute. The seemup_vector is a stroll that sojourns barely one row or one support. The computes in seemup_vector can be citation, gum, or argumentative computes. It is twinklingous that the computes in the seemup_vector must be settled in ascconposteriority enjoin such as: -3, -2, -1, 0, 1, 2, 3… a, b, c, d… FALSE, TRUE Otherwise, LOOKUP may not imsever the rectify compute. Upperpvoid and inferiorpvoid citations are equipollent. The adaptedty_vector is a stroll that sojourns barely one row or support. It must be the tallying bigness as seemup_vector. If LOOKUP cannot furnish the seemup_value, it competitiones the vastst compute in seemup_vector that is near than or tallyent to seemup_value. If seemup_compute is inexpressiveer than the inexpressiveest compute in seemup_vector, LOOKUP imparts the #N/A mistake compute. Let’s seem at the thriveing in of how to use the vector fashion of LOOKUP office. Figure 7.2: LOOKUP vector fashion You fashion all the citation trustworthys and sum trustworthys into the is-suesheet. You fashion in the thriveing fashionula into cell A9: =LOOKUP(A7,A2:A4,B2:B4) You get #N/A consistently Excel treats an void cell as naught compute. Past naught compute is near than the inexpressiveest compute from the seemup_vector, thus it accomplish adaptedty the not adapted axioms. You can try the thriveing input from consultation 7.1 on cell A7 to see the incongruous adaptedtys in cell A9. 84 A7 3.98 3.99 4 4.6 10 A9 #N/A Pen Pen Marker Duster Table 7.1: LOOKUP vector computes If you seem circumspectly, you may mention that you can use the LOOKUP vector to mould resolutions. For in, you can geneobjurgate a undesigning Excel is-sueshuffle to state which ace you can acquisition thriveing a end an quantity of currency you feel in your wallet. By penetrateing a compute into a cell, the LOOKUP office can exploration for the vocableinacriterion compute from the register and then profits a compute of the competitioned seemup of the tallying row. Now, let’s seem at the direct LOOKUP office. The dispose fashion of LOOKUP seems in the peculiar row or supmien of an dispose for the profital compute and profits a compute from the tallying standing in the definite row or supmien of the dispose. The vector syntax fashion is: =LOOKUP(lookup_value, dispose) The seemup_compute is a compute that LOOKUP explorationes for in an dispose. The seemup_compute can be a sum, citation, a argumentative compute, or a state or relation that connects to a compute. If LOOKUP can't furnish the seemup_value, it uses the vastst compute in the dispose that is near than or tallyent to seemup_value. If seemup_compute is inexpressiveer than the inexpressiveest compute in the peculiar row or supmien (depconposteriority on the dispose work), LOOKUP profits the #N/A mistake compute. Dispose is a stroll of cells that sojourns citation, gum, or argumentative computes that you nonproduction to parallel thriveing a end seemup_value. The dispose fashion of LOOKUP is very alove to the HLOOKUP and VLOOKUP offices. The estrangement is that HLOOKUP explorationes for seemup_compute in the peculiar row, VLOOKUP explorationes in the peculiar support, and LOOKUP explorationes according to the work of dispose. If the dispose covers an area that is ampler than summit (such as alikeize supports than rows), then LOOKUP explorationes for seemup_compute in the peculiar row. If the dispose is taller than its width (equalize rows than supports), LOOKUP explorationes in the peculiar support. Forthadvenient a end HLOOKUP and VLOOKUP, you can refutation down or despite, but LOOKUP frequently excellents the definite compute in the row or support. We feel mellow HLOOKUP and VLOOKUP offices in Article 5. You can connect end to Article 5 to refashion end your reminiscence on how to use HLOOKUP and VLOOKUP offices. 7.7 Dispose Function Rank office profits dispose of a sum in a register. This office requires 2 arguments and 1 libertyal. The thriveing syntax professions the dispose office: =RANK(number, ref, enjoin) The sum is the sum for which you nonproduction to furnish its dispose. It can be a cell relation. The ref is an dispose, or a relation to, a register of gum. Nonnumeric computes are ignored. The enjoin is a sum. If enjoin parameter compute is 0 or omitted, the dispose in the register is schemeationed descending. If enjoin parameter compute is nonzero, the dispose in the register schemeationed in ascconposteriority enjoin. Figure 7.3 professions how dispose office is used. 85 Figure 7.3: Dispose office Ranking is beneficial when you nonproduction to see what registers are most favored in a bulky. You may gard disposeing is alove to Top 10 of AutoFilter exclude disposeing does not reaffect other registers. 7.8 PivotTable & PivotChart When you are established thriveing a end bulky register, tshort are seasons that you nonproduction to geneobjurgate a incorporated remien of consultation and chart confer-upsingly naturalized on bunching. PivotTable remien and PivotChart remien are the deep ingredient you should use in Microsoft Excel. A PivotTable remien is an interlocomotive consultation that confer-upsingly coalesces and parallels vast quantitys of axioms. You can circulate its rows and supports to see incongruous summaries of the rise axioms, and you can vault the specialtys for areas of concern. PivotTable is-sues in such a way whereby it computes abridgment statistics of bunched categories. A PivotChart remien yields a picturesqueal stateation of the axioms in a PivotTable relation. You can fluctuate the layout and axioms vaulted in a PivotChart remien adapted as you can in a PivotTable relation. Let’s see how we can use PivotTable and PivotChart signs to our utility. Fascinate thrive the counsels adown to exhaustive the register precedently you can use PivotTable and PivotChart. 1) Geneobjurgate a register naturalized on Figure 7.4. 86 Figure 7.4: University A template 2) Now, we accomplish provide adding axioms conclusiveation onto each supmien provideing from row 3 plow 20. Set a citation tediousness axioms conclusiveation that sanctions a tediousness natant 3 and 30 missives for supmien A. 3) At cell B30 and B31, fashion in Diploma and Degree. 4) Starting at cell C30 afront, fashion in Architecture, Biology, Business, Chemistry, Communication, Picturesque Design, IT, Medicine and Physics. 5) Add a register axioms conclusiveation for supmien C naturalized on cell C30 plow C38. 6) Add axioms conclusiveation for supmien D that sanctions decimal gum natant 0 and 60. 7) Add axioms conclusiveation for supmien E that sanctions decimal gum natant 0 and 40. 8) Fashion in the fashionula of =D3+E3 into cell F3. Then you resemblance the fashionula until row 20. 9) Connect to Figure 7.5, fashion in the citation and sum trustworthys into those cells. 10) Fashion in the fashionula of =VLOOKUP(F3, $E$31:$G$40, 3, TRUE) into cell G3. Then you resemblance the fashionula until row 20. 11) Fashion in the fashionula of =IF(G3="F", "Fail", IF(B3="Diploma", IF(AND(D3>=30, E3>=20), "Pass", "Fail"), IF(B3="Degree", IF(OR(D3>=50, (D3+E3)>=50), "Pass", "Fail"), "Nil"))) into cell H3. Then you resemblance the fashionula until row 20. 87 Figure 7.5: VLOOKUP consultation axioms 12) Excellent any of the cells on the register. Figure 7.6: Rise axioms 13) You fashion in the axioms by thriveing Figure 7.6. Unintermittently you feel high, conposteriority on to tcomprehend 14. 14) Click on Axioms menu and pick-out PivotTable and PivotChart Remien bid. 88 15) At tcomprehend 1 of the PivotTable and PivotChart Remien Wizard, you pick-out Microsoft Excel register or axiomsbase to excite, and pick-out PivotChart remien (forthadvenient a end PivotTable relation) liberty. 16) At tcomprehend 2, you adapted click Direct trifle past Microsoft Excel effortlessally excellents the completion register. The stroll of the register is $A$2:$H$20. 17) At tcomprehend 3, you put the PivotTable remien and PivotChart remien on new is-suesheet. 18) Unintermittently you feel exhaustived the 3 treads, you accomplish see a utter PivotChart as professionn in Figure 7.7. 19) Restate Chart1 to PivotChart and Sheet1 to PivotTable. 20) Aim PivotTable is-suesheet. Before you provide diversifying the utter PivotTable, you must imply the ingredients of PivotTable which feel axioms, arena fashions and layout. 7.8.1 Data • Source axioms – The adownlying rows or axiomsbase registers that yield the axioms for a PivotTable relation. You can geneobjurgate a PivotTable remien from a Microsoft Excel register, an manifest axiomsbase, multiple Excel is-suesheets, or another PivotTable relation. Figure 7.6 professions the rise axioms for our PivotTable and PivotChart relations. • Field – A classify of axioms that's moderate from a arena in the rise register or axiomsbase. The Name, Fashion of Course, Arena of Study are some ins of arenas. The arena sojourns states from the rise axioms. E.g. Fashion of Round has Diploma and Quantity round. • Item – A subcategory, or constituent, of a arena. Items state the choice entries from the arena in the rise axioms. For in, Diploma, Degree, Architecture, Biology, A, A+, B-, F, etc are aces. • Summary office – The fashion of estimation used to coalesce computes in a axioms arena. PivotTable relations usually use Sum for axioms arenas that redeep gum and Reckon for axioms arenas that redeep citation. You can excellent concomitant abridgment offices such as Average, Min, Max, and Emanation to proelement the aces, • trifle is a bid to upspell a PivotTable remien thriveing a end Refresh – This the most new axioms from the rise register or axiomsbase. 7.8.2 Arena Types • Row arena – You can connect to Figure 7.9 to see wshort row arena is establishd. A PivotTable remien that has alikeize than one row arena has one obstruct row arena (Field of Study is the obstruct row arena), the one vocableinacriterion to the axioms area. Any other row arenas are manifest row arenas (Type of Round is the manifest row arena). Items in the manifestmost row arena are vaulted barely unintermittently, but aces in the tranquillity of the row arenas are iterateed as nonproductioned. 89 • Column arena – You can connect to Figure 7.9 to see wshort supmien arena is establishd. • Page arena – It confesss you to percolate the completion PivotTable remien to vault axioms for a uncollectively ace or all aces. • Data arena – Axioms arenas yield the axioms computes to be incorporated. Usually axioms arenas redeep gum, which are coalesced thriveing a end the Sum abridgment office, but axioms arenas can so redeep citation, in which pvoid the PivotTable remien uses the Reckon abridgment office. If a remien has alikeize than one axioms arena, a uncollectively arena trifle stated Axioms defenses in the remien for approximation to all of the axioms arenas. 7.8.3 Layout • Drop areas – The bluish projectd regions you see when you complete the treads of the PivotTable and PivotChart necromancer. To lay out a PivotTable relation, you pull arenas from the arena register window and ooze them onto the ooze areas. • Field register – A window that registers all of the arenas adapted from the rise axioms for use in the PivotTable relation. If a arena is constituted in projectes of specialty, you can click or to profession or screen the inferior projectes. To vault the axioms from a arena in the PivotTable relation, pull the arena from the arena register to one of the ooze areas. • Field ooze-down register – A register of the aces adapted for vault in a arena. If the arena is constituted in projectes of specialty, you can click or to see which inferior-plane aces are excellented for vault. A incork curb impression media that some or all of the inferior-plane aces are vaulted. • Data area – The sunderneath of a PivotTable remien that sojourns abridgment axioms for the row and supmien arenas. For in cell D7 professionn in Figure 7.9 is axioms area. • Indented fashionat – Figure 7.9 professions a fashionable fashionat of PivotTable. An hollowed fashionat seems love Figure 7.10. Now we accomplish provide diversifying the utter PivotTable. 21) Peculiar of all, you click and pull Fashion of Round arena and ooze it onto Row arena. 22) Then you click and pull Arena of Study arena ooze it onto Row arena but settle it to the straight of Fashion of Round arena. 23) Next, you click and pull Progression arena and ooze it onto Supmien arena. 24) Lastly, you click and pull Pass/Fail arena and ooze it onto Axioms arena. 25) Instil a row alikeize Page arena. Then you fashion in University A End of Semester Abridgment Report. 26) Forthadvenient that, you fashionat the PivotTable to mould the defenseance seems as alove as potential to Figure 7.9. 90 27) Then you switch to PivotChart tab. 28) You feel to diversify the chart a brief bit. Straight click on the Y axis and fashionat it. 29) At Scale tab, you mould abiding the repairments for Stint is 0, Apex is 5, Elder item is 1, Undignified item is 0.1. Then click Ok. 30) Straight click on any Arena register and then pick-out Screen PivotChart Arena trifles. You should get a PivotChart that seems love Figure 7.11 Figure 7.7: Utter PivotChart Figure 7.8: Utter PivotTable 91 Data arena Column arena Row arena Figure 7.9: Mitigated PivotTable Figure 7.10: Hollowed fashionat 92 Figure 7.11: PivotChart Summary Data separation is an twinklingous adowntaking to adaptedty beneficial recognizelaspect for rekey making. Tshort are numerous axioms separation techniques such as schemeationing, guarded, summarizing, percolateing, fashionatting and charting. Reckon offices are used to celebobjurgate round of frequencies. IF office can confess us to mould rekey out from two or alikeize potential outcomes. AND ; OR offices are argumentative offices to mould resolutions as polite-mannered-mannered-mannered as aim forthcoming and seemup office. PivotTable and PivotChart relations can confer-upsingly incorporate bulky quantity of axioms. 93 Chapter 8 Automating Repetitive Tasks 8.1 Macro 8.2 Recording Macro 8.3 Exoteric Macro 8.4 Buttons OBJECTIVE In this article, tyro accomplish compass: ? To imply and use macro to automate adowntakings ? To add peculiar macro is-suebook ? To add trifles thriveing a end built-in macro to run adowntakings 8.1 Macro If you engagementinate a adowntaking iterateedly in Microsoft Excel, you can automate the adowntaking thriveing a end a macro. A macro is a succession of bids and offices that are abundanced in a Microsoft Visual Basic module and can be run whenever you nonproduction to engagementinate the adowntaking. For in, if you frequently penetobjurgate hanker citation strings in cells, you can geneobjurgate a macro to fashionat those cells so that the citation infolds. Macros can be vaulted thriveing a end Visual Basic Editor (VBE). We can use VBE to generate, edit, run and debug (fix) Excel macros. Macro defenses as statements in VBE edict window. We accomplish not seem at macro coding in VBE at this projecte. 8.2 Recording Macro How do we automate adowntaking thriveing a end a macro? We annals out adowntakings thriveing a end macro. Recording macro is adapted love using the video recenjoin indicateer to annals a TV program onto a video tape. The video tape can indicate end intermittently and intermittently to see whatever video has been annalsed down. Before you nonproduction to provide annalsing a macro, you must project out circumspectly what adowntaking you nonproduction to be use to iteobjurgate alikeize and alikeize intermittently. Forthadvenient projectning, you provide annalsing the macro. You click on Tools menu Macro bid Record new macro 94 subcommand. You accomplish see the Annals Macro dialog box defenses as professionn in Figure 8.1. Whole macro must feel a choice state. The macro state cannot feel any utter quantity and it must provide thriveing a end a epistle. You can apprehend a shortcut key to confer-upsingly challenge this macro at any season. You may apprehend a beneficial cognomen to delineate a macro. The Abundance macro in favoringity, you feel 3 libertys to pick-out from. • Personal Macro Workbulk – If you nonproduction a macro to be adapted whenever you use Excel, excellent Peculiar Macro Workbook. A is-suebulk that unconcealeds effortlessally whenever Excel is unconcealeded. Any macro abundanced in this is-suebulk can be used by any unconcealed is-suebook. • New Workbulk – Abundance new macro in new is-suebook. • This Workbulk – Abundance new macro in locomotive is-suebook. Figure 8.1: Annals Macro dialog box While annalsing a macro, whole uncollectively tread, mouse clicks and keystrokes accomplish be annalsed down into that macro. If you nonproduction the macro to run not-arbitrary to the standing of the locomotive cell, annals it using not-arbitrary cell relations. On the Seal Recording machinebar (Figure 8.2), click Not-arbitrary Relation so that it is excellented. Excel accomplish endure to annals macros thriveing a end not-absolute references until you discharge Excel or until you click Not-arbitrary Relation intermittently, so that it is not excellented. Unintermittently you feel high engagementinateing your adowntakings, you click on seal annalsing macro trifle. Stop annalsing Relative Reference Figure 8.2: Seal Recording machinebar 8.3 Exoteric Macro Once you feel exhaustived annalsing a macro, you should try exoteric it. Tshort are a few ways to run macros: • Shortcut keys • Indicate macro (ALT + F8) bid to excellent any penny macros • Challenge macro using trifles If you nonproduction to run macro using shortcut keys, you feel to state the shortcut key when you geneobjurgate new macro. Penetobjurgate a epistle in the Shortcut key box. You can use CTRL+ epistle (for inferiorpvoid epistles) or CTRL+SHIFT+ epistle (for upperpvoid epistles), wshort epistle is any epistle key on the keyboard. The shortcut key epistle you use cannot be a sum or special 95 missive such as @ or #. The shortcut key accomplish alikeizeride any equipollent lapse Microsoft Excel shortcut keys end the is-suebulk that sojourns the macro is unconcealed. If you indicate macro by urgent-compulsory ALT + F8 keys, you accomplish see Macro dialog box as professionn in Figure 8.3. Run excellented macro one cord of edict at a season Edit macro using VBE Asexpression macro forthadvenient a end shortcut key or edit description Figure 8.3: Macro dialog box Select the macro that you nonproduction to run and then click on Run trifle. The avail you clicked on the Run trifle, Excel confer-upsingly runs all coding cord of the macro thriveing a endin few relieves. If you cannot see what adowntakings ran, you can use the Tcomprehend Into trifle to run the macro one cord of edict at a season. If you nonproduction to run a macro from a trifle, connect to 8.4 Buttons subject-matter. 8.4 Buttons Tshort are two fashions of trifles that you can add into Microsoft Excel is-suebook. One is to add trifles onto is-sueshuffle and the other is adventitious to machinebar as a useize trifle. Adding trifles onto is-sueshuffle is undesigning. Vault the Fashion machinebar. Click on the Trifle icon and then induce out a trifle bigness on the is-suesheet. Unintermittently you feel inducen out a trifle, Excel confer-upsingly unconcealeds up Asexpression Macro dialog box. You can asexpression a macro to the trifle now or posterior. It is recommended to tranquillityate the trifle to a mediocreingful state by straight clicking on the trifle and pick-out Edit Text. To affect a trifle, you feel to straight click on the trifle to get the excellention brink locomotive. Then click on the brink and the affect the trifle encircling. Button Figure 8.4: Forms machinebar 96 Macros annalsed and abundanced superveneing a endin workbook Button brink indicates trifle is excellented Drawn trifle on is-suesheet Figure 8.5: Asexpression Macro to trifle Another fashion of trifle settled on is-sueshuffle is a clickable picturesque aim. You use the Sketch machinebar to induce out patterns, aims or picturesqueal citation peculiar, and then you straight click on that aim and pick-out Asexpression Macro liberty. Then you asexpression a macro to this aim; adapted love specifying a macro to a trifle. Figure 8.6 professions a inducen arrow pattern is popularly nature specifyed thriveing a end a macro. Assigning macro to inducen pattern Figure 8.6: Asexpression Macro to pattern/object The relieve way to asexpression macros is to asexpression them to trifles on useized machinebar. Peculiar of all, approximation Customize bid from Tools menu. Geneobjurgate a new machinebar by giving a mediocreingful state. Then click on Commands tab. Excellent Macros adown Categories. Click and pull out one of the bid aces onto the new machinebar. Forthadvenient you vocableinate Customize dialog box, when you click on the trifle on the new machinebar, Asexpression Macro dialog box defenses to confess you to asexpression a macro to that trifle precedently you can use it. 97 Create new toolbar Adds custom toolbar to workbook Added new toolbar Figure 8.7: Customize machinebar Click and pull bid onto new machinebar Figure 8.8: Adding use macros trifle You can diversify the trifle such as giving a adapted state; fluctuate the trifle mienraiture, etc. Figure 8.9 professions a use trifle is encircling to be mitigated. 98 Figure 8.9: Customizing use macro trifle on new machinebar Summary Macro is a succession of bids annalsed and abundanced superveneing a endin is-suebulk that you can use it to automate adowntakings keener. Macro can be generated by using Visual Basic Editor or creating a new macro to provide for annalsing. End annalsing a macro, any actions, adowntakings, bids, keystrokes and mouse clicks engagementinateed, they are annalsed and abundanced superveneing a endin a macro. Each macro has a choice state, can be specifyed thriveing a end shortcut keys, and has cognomen and abundance in any one of three colonizations. Macros can be run in diverse ways: shortcut keys, trifles on is-sueshuffle and trifles on machinebar. Adding trifles to is-suesheets are not-hard and they are pliable. You can induce out patterns and add macros onto the patterns. Toolbars are not-hard to add and useize thriveing a end macros. 99 BIBLIOGRAPHY Robert T. Grauer, Maryann Barber. 2005, Exploring Microsoft Excel 2003 Revised, Revised edition, Prentice Hall, New Jersey H. Albert Napier, Philip J. Judd, Benjamin Rand. 2002, Mastering and using Microsoft Excel 2002, peculiar edition, Round Technology, Massachusetts Philip A. Koneman. 2001, Projects For Microsoft Excel 2000 Advanced, peculiar edition, Prentice Hall, Upper Saddle River, New Jersey WEBSITES Excel Tutorial - http://www.usd.edu/trio/tut/excel/ TECH on the Net - http://www.techonthenet.com/excel/index.php Meadinkent.co.uk - http://www.meadinkent.co.uk/excel.htm Tutorialized.com - http://www.tutorialized.com/tutorials/MS-Excel/1 FunctionX - http://www.functionx.com/excel/index.htm