SOFAPP Chapter 7: Advanced Operations with Excel PDF

MODULE OFFICE APPLICATION SOFTWARE - SOFAPP CHAPTER 7: Advanced Operations with Excel Objectives: a.) Determine the advanced operations that can be used in Microsoft Excel. b....

MODULE OFFICE APPLICATION SOFTWARE - SOFAPP CHAPTER 7: Advanced Operations with Excel Objectives: a.) Determine the advanced operations that can be used in Microsoft Excel. b.) Create a workbook that can be compatible with any software or office tools and make them more secured on sharing files. Lesson 1: Using Macro in Excel Macros in MS Excel Macros enable you to automate almost any task that you can undertake in Excel 2010. By using macro recorder from View Tab » Macro Dropdown to record tasks that you perform routinely, you not only speed up the procedure considerably but you are assured that each step in a task is carried out the same way each and every time you perform a task. To view macros choose View Tab » Macro dropdown. Macro Options View tab contains a Macros command button to which a dropdown menu containing the following three options. Page 1  MODULE OFFICE APPLICATION SOFTWARE - SOFAPP  View Macros − Opens the Macro dialog box where you can select a macro to run or edit.  Record Macro − Opens the Record Macro dialog box where you define the settings for your new macro and then start the macro recorder; this is the same as clicking the Record Macro button on the Status bar.  Use Relative References − Uses relative cell addresses when recording a macro, making the macro more versatile by enabling you to run it in areas of a worksheet other than the ones originally used in the macro’s recording. Creating Macros You can create macros in one of two ways −  Use MS Excel’s macro recorder to record your actions as you undertake them in a worksheet.  Enter the instructions that you want to be followed in a VBA code in the Visual Basic Editor. Now let's create a simple macro that will automate the task of making cell content Bold and apply cell color.  Choose View Tab » Macro dropdown.  Click on Record Macro as below.  Now Macro recording will start.  Do the steps of action, which you want to perform repeatedly. Macro will record those steps. Page 2  MODULE OFFICE APPLICATION SOFTWARE - SOFAPP  You can stop the macro recording once done with all steps. Edit Macro You can edit the created Macro at any time. Editing macro will take you to the VBA programming editor. Page 3  MODULE OFFICE APPLICATION SOFTWARE - SOFAPP Lesson 2: Cross Referencing in Excel Graphic Objects in MS Excel When you have information spread across several different spreadsheets, it can seem a daunting task to bring all these different sets of data together into one meaningful list or table. This is where the Vlookup function comes into its own. VLOOKUP VlookUp searches for a value vertically down for the lookup table. VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) has 4 parameters as below.  lookup_value − It is the user input. This is the value that the function uses to search on.  The table_array − It is the area of cells in which the table is located. This includes not only the column being searched on, but the data columns for which you are going to get the values that you need.  Col_index_num − It is the column of data that contains the answer that you want.  Range_lookup − It is a TRUE or FALSE value. When set to TRUE, the lookup function gives the closest match to the lookup_value without going over the lookup_value. When set to FALSE, an exact match must be found to the lookup_value or the function will return #N/A. Note, this requires that the column containing the lookup_value be formatted in ascending order. VLOOKUP Example Let's look at a very simple example of cross-referencing two spreadsheets. Each spreadsheet contains information about the same group of people. The first spreadsheet has their dates of birth, and the second shows their favorite color. How do we build a list showing the person's name, their date of birth and their favorite color? VLOOOKUP will help in this case. First of all, let us see data in both the sheets. This is data in the first sheet Page 4  MODULE OFFICE APPLICATION SOFTWARE - SOFAPP This is data in the second sheet Page 5  MODULE OFFICE APPLICATION SOFTWARE - SOFAPP Now for finding the respective favorite color for that person from another sheet we need to vlookup the data. First argument to the VLOOKUP is lookup value (In this case it is person name). Second argument is the table array, which is table in the second sheet from B2 to C11. Third argument to VLOOKUP is Column index num, which is the answer we are looking for. In this case, it is 2 the color column number is 2. The fourth argument is True returning partial match or false returning exact match. After applying VLOOKUP formula it will calculate the color and the results are displayed as below. As you can see in the above screen-shot that results of VLOOKUP has searched for color in the second sheet table. It has returned #N/A in case where match is not found. In this case, Andy's data is not present in the second sheet so it returned #N/A. Page 6  MODULE OFFICE APPLICATION SOFTWARE - SOFAPP Lesson 3: Email Workbook Email Workbook MS Excel allows you to email the workbook very easily. To email the workbook to anyone, follow the below mentioned steps.  Choose File » Save and Send. It basically saves the document first and then the emails.  Click on Send using E-mail if your email system is configured. MS Outlook will open with the file as attachment in the New Email Window. You can send mail this workbook to anyone with valid email address. Page 7  MODULE OFFICE APPLICATION SOFTWARE - SOFAPP Lesson 4: Workbook Security We can apply security to the workbook by the concept of protection available in the Review Tab of ribbon. MS Excel's protection-related features fall into three categories.  Worksheet protection − Protecting a worksheet from being modified, or restricting the modifications to certain users.  Workbook protection − Protecting a workbook from having sheets inserted or deleted, and also requiring the use of password to open the workbook. Protect Worksheet You may want to protect a worksheet for a variety of reasons. One reason is to prevent yourself or others from accidentally deleting the formulas or other critical data. A common scenario is to protect a worksheet, so that the data can be changed, but the formulas can’t be changed. To protect a worksheet, choose Review » Changes group » Protect Sheet. Excel displays the Protect Sheet dialog box. Note that providing a password is optional. If you enter a password, that password will be required to unprotect the worksheet. You can select various options in which the sheet should be protected. Suppose we checked Format Cells option then Excel will not allow to format cells. When somebody tries to format the cells, he or she will get the error as shown in the screenshot below. Page 8  MODULE OFFICE APPLICATION SOFTWARE - SOFAPP To unprotect a protected sheet, choose Review » Changes group » Unprotect Sheet. If the sheet was protected with a password, you’re prompted to enter that password. Protecting a Workbook Excel provides three ways to protect a workbook.  Requires a password to open the workbook.  Prevents the users from adding sheets, deleting sheets, hiding sheets, and unhiding sheets.  Prevents users from changing the size or position of windows. Requiring a Password to Open a Workbook Excel lets you save a workbook with a password. After doing so, whoever tries to open the workbook, must enter the password. To add a password to a workbook, follow these steps.  Choose File » Info » Protect Workbook » Encrypt With Password. Excel displays the Encrypt Document dialog box.  Type a password and click OK.  Type the password again and click OK.  Save the workbook. Page 9  MODULE OFFICE APPLICATION SOFTWARE - SOFAPP To remove a password from a workbook, repeat the same procedure. In Step 2, however, delete the existing password symbols. Protecting Workbook’s Structure and Windows To prevent others (or yourself) from performing certain actions in a workbook, you can protect the workbook’s structure and windows. When a workbook’s structure and windows are protected, the user may not Add a sheet, Delete a sheet, Hide a sheet, unhide a sheet, etc., and may not be allowed to change the size or position of a workbook’s windows respectively. To protect a worksheet’s structure and windows, follow the below mentioned steps.  Choose Review » Changes group » Protect Workbook to display the Protect Workbook dialog box.  In the Protect Workbook dialog box, select the Structure check box and Windows check box.  (Optional) Enter a password.  Click OK. Page 10 MODULE OFFICE APPLICATION SOFTWARE - SOFAPP Page 11  MODULE OFFICE APPLICATION SOFTWARE - SOFAPP Lesson 5: Data Tables In Excel, a Data Table is a way to see different results by altering an input cell in your formula. Data tables are available in Data Tab » What-If analysis dropdown » Data table in MS Excel. Data Table with Example Now, let us see data table concept with an example. Suppose you have the Price and quantity of many values. Also, you have the discount for that as third variable for calculating the Net Price. You can keep the Net Price value in the organized table format with the help of the data table. Your Price runs horizontally to the right while quantity runs vertically down. We are using a formula to calculate the Net Price as Price multiplied by Quantity minus total discount (Quantity * Discount for each quantity). Page 12  MODULE OFFICE APPLICATION SOFTWARE - SOFAPP Now, for creation of data table select the range of data table. Choose Data Tab » What-If analysis dropdown » Data table. It will give you dialogue asking for Input row and Input Column. Give the Input row as Price cell (In this case cell B3) and Input column as quantity cell (In this case cell B4). Please see the below screen-shot. Clicking OK will generate data table as shown in the below screen-shot. It will generate the table formula. You can change the price horizontally or quantity vertically to see the change in the Net Price. Page 13  MODULE OFFICE APPLICATION SOFTWARE - SOFAPP Lesson 6: Pivot Tables Pivot Tables A pivot table is essentially a dynamic summary report generated from a database. The database can reside in a worksheet (in the form of a table) or in an external data file. A pivot table can help transform endless rows and columns of numbers into a meaningful presentation of the data. Pivot tables are very powerful tool for summarized analysis of the data. Pivot tables are available under Insert tab » PivotTable dropdown » PivotTable. Pivot Table Example Now, let us see Pivot table with the help of example. Suppose you have huge data of voters and you want to see the summarized data of voter Information per party, then you can use the Pivot table for it. Choose Insert tab » Pivot Table to insert pivot table. MS Excel selects the data of the table. You can select the pivot table location as existing sheet or new sheet. This will generate the Pivot table pane as shown below. You have various options available in the Pivot table pane. You can select fields for the generated pivot table. Page 14  MODULE OFFICE APPLICATION SOFTWARE - SOFAPP  Column labels − A field that has a column orientation in the pivot table. Each item in the field occupies a column.  Report Filter − You can set the filter for the report as year, then data gets filtered as per the year.  Row labels − A field that has a row orientation in the pivot table. Each item in the field occupies a row.  Values area − The cells in a pivot table that contain the summary data. Excel offers several ways to summarize the data (sum, average, count, and so on). After giving input fields to the pivot table, it generates the pivot table with the data as shown below. Page 15  MODULE OFFICE APPLICATION SOFTWARE - SOFAPP Lesson 7: Pivot Charts Pivot Charts A pivot chart is a graphical representation of a data summary, displayed in a pivot table. A pivot chart is always based on a pivot table. Although Excel lets you create a pivot table and a pivot chart at the same time, you can’t create a pivot chart without a pivot table. All Excel charting features are available in a pivot chart. Pivot charts are available under Insert tab » PivotTable dropdown » PivotChart. Pivot Chart Example Now, let us see Pivot table with the help of an example. Suppose you have huge data of voters and you want to see the summarized view of the data of voter Information per party in the form of charts, then you can use the Pivot chart for it. Choose Insert tab » Pivot Chart to insert the pivot table. Page 16  MODULE OFFICE APPLICATION SOFTWARE - SOFAPP MS Excel selects the data of the table. You can select the pivot chart location as an existing sheet or a new sheet. Pivot chart depends on automatically created pivot table by the MS Excel. You can generate the pivot chart in the below screen-shot. For more knowledge about Advanced Excel Tutorial , please check the link provided; https://www.youtube.com/watch?v=ezA8RPqkExY Page 17  MODULE OFFICE APPLICATION SOFTWARE - SOFAPP REFERENCES https://www.tutorialspoint.com/excel/excel_using_macros.htm https://www.tutorialspoint.com/excel/excel_cross_referencing.htm https://www.tutorialspoint.com/excel/excel_email_workbooks.htm https://www.tutorialspoint.com/excel/excel_workbook_security.htm https://www.tutorialspoint.com/excel/excel_data_tables.htm https://www.tutorialspoint.com/excel/excel_pivot_tables.htm https://www.tutorialspoint.com/excel/excel_pivot_charts.htm Page 18