Transcription
Microsoft Excel 2013By Rashid Rizwi(serving Excel training for 25 years)
Table of ContentsAbout Excel. 1About Excel . 1The Excel 2013 Ribbon . 1Interacting with Excel 2013 . 6Changing default settings . 6Introduction Excel 2013 . 8To Rename a Worksheet: . 8To Insert a New Worksheet: . 9To Delete a Worksheet: . 10TTo Copy a Worksheet: . 11To Move a Worksheet: . 13To Change the Worksheet Colour: . 14Grouping and Ungrouping Worksheets. 16To Group Worksheets:. 16To Ungroup All Worksheets: . 17Understanding Cell Reference and Range Name . 18Using relative references . 18Limitations of relative references . 18Absolute references . 18Mixed references . 18Working with Range Name . 19Working with general used Excel Function . 21Using Formulas in a Worksheet . 21SUM Function . 21AutoSum . 22AVERAGE Function . 22MAX & MAXA Function . 23MIN & MINA Function . 25LARGE Function. 28SMALL Function . 30PRODUCT Function . 31SQUARE root and CUBE root . 34RANK Function. 35RANK.AVG Function . 36Working with Logical Functions . 39IF Function . 40Nesting Function using IF . 41IF function [Using And/ Or/ Not] . 42Sorting and Filtering Data . 43Performing a Simple Sort . 43Customized Sorting . 44First we need to create a custom list as we want to sort, . 44Filtering a List . 47Filtering a List using Advanced Filter . 48Filtering unique records . 48Advanced Formatting Features . 48This document is designed by Rashid Rizwi, email: contactus@marstraining.in rashidraj97@gmail.com, website: www.marstraining.in , Voice: 91 9870259245 919867356452
AutoFormat. 49Conditional formatting . 49Working with LOOKUP Function . 57Vlookup . 57Hlookup . 58Database Functions . 60DSUM. 60DSUM. 61DCOUNT . 61DMAX . 61DMIN . 61DAVERAGE . 61Financial Functions. 61PMT . 61PPMT Function. 62IPMT Function . 63FV . 64Data Validation . 66Designate valid cell entries . 66Protecting Workbook . 69Protecting a worksheet by using passwords . 69Protecting part of a worksheet . 69Built-in templates . 71Creating and managing templates. 71Creating and using a template . 71Modifying templates . 71Guidelines for entering data in a worksheet . 72Data organization. 72Data format . 72About Data lists . 72Grouping Data Using Subtotals . 75Display Subtotal at Single Level . 75Displaying Nested Subtotals . 76Using add-ins - Conditional Sum Wizard . 76Creating PivotTables . 78Steps to Create a Simple Pivot Table . 78Format a PivotTable report . 81Calculate the Percentage of the field . 81Group items in a PivotTable . 82Create a Graph using Pivot Data . 82What-if-Analysis Tools . 83Using the Goal Seek Command . 83Projecting Figures Using a Data Table . 84Using a One-Input Data Table . 85Using a Two-Input Data Table . 86What-If Scenarios . 88Create a scenario. 88Create a scenario summary report . 89This document is designed by Rashid Rizwi, email: contactus@marstraining.in rashidraj97@gmail.com, website: www.marstraining.in , Voice: 91 9870259245 919867356452
Delete a scenario. 89Display a scenario. 89Merge scenarios from another worksheet . 90Protecting Scenarios . 90Using Multiple Worksheets . 91Linking worksheets by using 3-D formulas. 91Consolidating Data . 91Creating Hyper Link . 93Creating links between different worksheets. . 93Creating links between different software . 93Data Form . 95Add a record . 95Modify a record . 96Delete a record . 96Auditing features. 97Tracing errors in a worksheet . 97Workgroup collaboration . 98Sharing workbooks . 98Merging workbooks . 99Tracking changes . 99Macros. 101What is Macro?. 101How to Start Recording a Macro . 101Other Options of Macro . 102Running a Macro Using Menu Commands . 102How to insert button into worksheet and assigning Macro to a Button . 103Short Cut Keys For Excel 2013. 103This document is designed by Rashid Rizwi, email: contactus@marstraining.in rashidraj97@gmail.com, website: www.marstraining.in , Voice: 91 9870259245 919867356452
This document is designed by Rashid Rizwi, email: contactus@marstraining.in rashidraj97@gmail.com, website: www.marstraining.in , Voice: 91 9870259245 919867356452
Excel 2013Worksheet BasicsAbout ExcelAbout ExcelMicrosoft Excel is a powerful Worksheet computing tool from Microsoft Corporation. It makes it easy foryou to create various kinds of spreadsheets, tables and statements along with the graphical representationof data using graphs. While working in Excel, you can make use of its most important feature ofautomatic recalculation to save time and effort.In Excel, you work with worksheets, which consist of rows and columns that intersect to form cells. Cellscontain various kinds of data that you can format, sort, and analyze. You can also create charts based onthe data contained in cells. An Excel file is called a workbook, which by default contains threeworksheets.The Excel 2013 RibbonFirst impression of the 2013 ribbon was that it was cleaner and maybe a little smaller than in2007. It is clear that the new ribbon was no smaller than before, still a good row taller than anoverloaded toolbar area in 2003. But the 2013 ribbon definitely is cleaner.This document is designed by Rashid Rizwi, email: contactus@marstraining.in rashidraj97@gmail.com, website: www.marstraining.in , Voice: 91 9870259245 9198673564521
The 2003 interface is pretty cluttered, efficient interface that displays all of my most-usedcommands at once, without hiding 90% of them. The 2007 ribbon looks nearly as cluttered as theclassic toolbar interface, not because it has lots of controls, but because everything is in boxes,and there is no shortage of graded coloring.The 2013 ribbon has the same density of controls as in 2007, but it looks cleaner because of itslighter background and the elimination of much of the chart-junk-like features. Instead of boxesaround all of the groups of controls, as in 2007, the groups are separated by a single light line,and the shadowing is much less pronounced.A Designer Cleans up the 2007 RibbonA year ago Andreas Lipphardt of XLCubed wrote Microsoft, Pimp Down My Ribbon, a requestto clean up the 2007 ribbon. He even showed how. He started with the ribbon out of the box:This document is designed by Rashid Rizwi, email: contactus@marstraining.in rashidraj97@gmail.com, website: www.marstraining.in , Voice: 91 9870259245 9198673564522
Andreas cleaned up the ribbon by removing all the gradients and glows, and without all of thegratuitous effects, it certainly looked much cleaner.Microsoft stopped here with the 2013 ribbon, but Andreas took it one step further, removing thelarge office button and the oversized ribbon buttons (e.g., Paste), and closed up the excess space.This was becoming too much like the classic interface.Why Can’t Users Clean Up Their Own Ribbons?Now they can, to some extent. Microsoft has added the ability to make changes to the interfacethrough the interface. Even though RibbonX, the ribbon-specific subset of XML, is relativelyeasy to use to customize the interface, RibbonX is still way beyond a typical user’s capabilities.This ribbon customization capability gives back to the user some small measure of control,maybe 10% of what we once had.This document is designed by Rashid Rizwi, email: contactus@marstraining.in rashidraj97@gmail.com, website: www.marstraining.in , Voice: 91 9870259245 9198673564523
Charting on the RibbonI’ll show a couple shots to further illustrate the cleaner ribbon appearance in 2013. Here are theCharts groups from the Insert tabs of Excel 2007 and 2013.Although the 2013 group looks much cleaner, the buttons are all the same as before, and in fact,all the options under the buttons are the same. The gallery of chart types in 2013 is just like thatin 2007. The Chart Tools contextual tabs are also cleaner but with essentially no real changes.The one addition is a Draft Mode button on the Design tab. Its purpose is to allow charts to bedrawn with only rudimentary formatting, to speed up screen refreshing in Excel 2007.This document is designed by Rashid Rizwi, email: contactus@marstraining.in rashidraj97@gmail.com, website: www.marstraining.in , Voice: 91 9870259245 9198673564524
A Couple New Features – Look But Don’t TouchHowever, right next to the Charts group on the Excel 2013 Insert tab are two new groups:Sparklines and Filter, which houses the Slicer. These much heralded features are probably themost talked about new tricks in Excel 2013. The advance press for Sparklines on the MSDNExcel blog is very promising.At first I thought that neither feature is enabled for testing in this early preview version. I didn’tnotice that I was using Compatibility Mode, which disables advanced features introduced in2007 and later.Chart DialogsI made a few charts, just to see what if anything has improved in the awkward Excel 2007dialogs. One thing has changed: after being temporarily retired for one version, the ability hasreturned to double click on a chart element to open its format dialog! Unfortunately the dialogsthat open up are unchanged from those of 2007. Too many tabs, too many options hidden indropdowns when there is ample space to use list boxes or option buttons, too many clicksrequired to do otherwise quick tasks. And the F4 (Repeat Last Action) command is still AWOL.It remains to be seen whether any changes will be forthcoming in upcoming releases of thepreview.I’ve been working on a charting interface add-in for Excel 2007, and I suspect that it will beuseful for users of Excel 2013 as well.Macro RecorderI recorded a few macros, nothing too complex, to see whether the huge gaps in Excel 2007′smacro recorder coverage have been addressed. In 2007 if you recorded a macro while doinganything with shapes, the macro came out blank. When working with charts, some actions wererecorded, but many were missed, particularly those related to formatting of the shapes that makeup the chart’s elements. Because the recorder didn’t record, we were without a tool that was sohelpful to decode the intricacies of the object model.Without examining the details, I was able to see that all actions were represented by commandsin the recorded macros. Shapes and chart element formatting were again represented in the code.This is a great (re)addition to a developer’s toolbox.SummaryThis document is designed by Rashid Rizwi, email: contactus@marstraining.in rashidraj97@gmail.com, website: www.marstraining.in , Voice: 91 9870259245 9198673564525
Excel 2013 looks a bit cleaner and seems to run a bit more smoothly than Excel 2007. In thesmall part of Excel that I have examined, very little has changed. I will be looking at Spark linesand other neat new stuff in the near future.Interacting with Excel 2013You interact with Excel by typing and by using the mouse to choose commands, make selections, andclick buttons and options.Using the RibbonThe Ribbon is the main location for menus and tools. When you choose a Ribbon tab, the Ribbon displaysRibbon groups that contain tools such as buttons and lists. Some of these tools expand to display simplelists, and some display galleries, as shown in Error! Reference source not found. A list is a collectionf related commands or selections. A gallery is an interactive list.Using galleriesSelections on a gallery display the result of the selections in Excel, rather than just a list of options. Somegalleries use live preview. When you move the pointer over options on a gallery, each option is previewedon whatever is selected on the worksheet. For example, if you select text in the worksheet, and display theFont gallery, moving the pointer over each font in the gallery causes the selected text on the screen todisplay in that font.Using toolsWhen you point to a tool, a description called a super tool tip appears. The super tool tip provides lessdescription than Help, but more than an ordinary screen tipChanging default settingsExcel allows you to change many aspects of its behavior and how you interact with it. You can changedefault settings such as number of iterations, font, file locations, and the file that opens on starting Excel.To select the dialog box of Options you need to click on File Tab- Option and select the required tabsfrom the dialog box.Personalize optionsYou can change workbook settings byusing the personalize options such as Typeof Font, Size of the font, number ofworksheet in workbook and can alsoactivate the Developer tab , which is usedfor Macro.Save optionThis document is designed by Rashid Rizwi, email: contactus@marstraining.in rashidraj97@gmail.com, website: www.marstraining.in , Voice: 91 9870259245 9198673564526
Save option allows you to change the default file location, FileFormat, and Auto save the file.This document is designed by Rashid Rizwi, email: contactus@marstraining.in rashidraj97@gmail.com, website: www.marstraining.in , Voice: 91 9870259245 9198673564527
Introduction Excel 2013Every workbook contains at least one worksheet by default. When working with a large amount ofdata, you can create multiple worksheets to help organize your workbook and make it easier to findcontent. You can also group worksheets to quickly add information to multiple worksheets at thesame time.To Rename a Worksheet:Whenever you create a new Excel workbook, it will contain oneworksheet named Sheet1. You canrename a worksheet to better reflect its content. In our example, we will create a training log organizedby month.1. Right-click the worksheet you wish to rename, then select Rename from the worksheetmenu.Clicking Rename2. Type the desired name for the worksheet.Entering a new worksheet name3. Click anywhere outside of the worksheet or press Enter on your keyboard. The worksheet will berenamed.This document is designed by Rashid Rizwi, email: contactus@marstraining.in rashidraj97@gmail.com, website: www.marstraining.in , Voice: 91 9870259245 9198673564528
The renamed worksheetTo Insert a New Worksheet:1. Locate and select the New sheet button.Clicking the New sheet button2.A new, blank worksheet will appear.The new, blank worksheetTo change the default number of worksheets, navigate to Backstage view, click Options, thenchoose the desired number of worksheets to include in every new workbook.This document is designed by Rashid Rizwi, email: contactus@marstraining.in rashidraj97@gmail.com, website: www.marstraining.in , Voice: 91 9870259245 9198673564529
Modifying the number of default worksheetsTo Delete a Worksheet:1. Right-click the worksheet you wish to delete, then select Delete from the worksheet menu.Deleting a worksheetThis document is designed by Rashid Rizwi, email: contactus@marstraining.in rashidraj97@gmail.com, website: www.marstraining.in , Voice: 91 9870259245 91986735645210
2. The worksheet will be deleted from your workbook.The deleted worksheetIf you wish to prevent specific worksheets from being edited or deleted, you can protectthem byright-clicking the desired worksheet and then selecting Protect sheet. from the worksheetmenu.Protecting a worksheetTTo Copy a Worksheet:If you need to duplicate the content of one worksheet to another, Excel allows you to copy anexisting worksheet.1. Right-click the worksheet you want to copy, then select Move or Copy. from the worksheetmenu.This document is designed by Rashid Rizwi, email: contactus@marstraining.in rashidraj97@gmail.com, website: www.marstraining.in , Voice: 91 9870259245 91986735645211
Selecting Move or Copy.2. The Move or Copy dialog box will appear. Choose where the sheet will appear in the Beforesheet: field.In our example, we'll choose (move to end) to place the worksheet to the right of the existingworksheet.3. Check the box next to Create a copy, then click OK.Copying a worksheet4. The worksheet will be copied. It will have the same title as the original worksheet, as well as aversionnumber. In our example, we copied the January worksheet, so our new worksheet isnamed January (2).This document is designed by Rashid Rizwi, email: contactus@marstraining.in rashidraj97@gmail.com, website: www.marstraining.in , Voice: 91 9870259245 91986735645212
All content
This document is designed by Rashid Rizwi, email: contactus@marstraining.in rashidraj97@gmail.com, website: www.marstraining.in , Voice: 91 9870259245 91 9867356452 1 Excel 2013 Worksheet Basics About Excel About Excel Microsoft Excel is a powerful Worksheet computing tool from Microsoft Corporation.
