Ch.19--"Outlines, Grouping, Subtotals and Data Validation"

Ch.19 for advanced Excel teaches you on using outline, group, subtotals and a bit of review on data validation. To create an outline in Excel go to data then group and outline. You have different options on what you want, such as you can auto outline the worksheet or you can group data as you wish. All you need to do is click on the types of groups or outlines you want. Subtotals are a rather easy feature on Excel. To use subtotals you highlight the data you wish to subtotal and go to data then subtotals. The subtotal feature allows you to subtotal by however you want according to your columns. You are able to find averages, counts and sums. This chapter also has a review on data validation. To use it you highlight what you wish to validate then go to data then validation. Just enter in what options you wish to have. You are able to circle already invalid data by opening the tools menu then formula auditing and then select show formula auditing toolbar.

The first project asks you to create an outline for an Auto Parts company number of parts sold. I did what you needed to do to create an outline and I chose auto outline and it looked good so I kept it. The second project asks you to make subtotals of the counts in each of the regions. Their way of subtotaling their data was not efficient enough so I changed the way to subtotal it. I eliminated all of the headings except for the top heading. I did add in the extra column to classify each item on what division it is a part of. Then I subtotaled it and it was more efficient and it looked better. The activity wants you to create a household inventory so I copied the inventory into Excel and used the sum feature on subtotal to create a subtotal for each category of household items.

Ch.18--"Shared Workbooks"

Chapter 18 involves shared workbooks. Shared workbooks are used in situations when several people need to work in a workbook at the same time. To share a workbook you go to the tools tab and choose share workbook and you check the box that allows the workbook to be worked on by others. Track changes in these shared workbooks are also a useful thing to have on shared workbooks. To start track changes open the tools menu and choose track changes, and then select highlight changes. You can add who can see the chances and where and when.

The first project wants you to create a shared workbook for GMRE Real Estate. So you open the document, save it and make it a shared workbook. I created three additional shared workbooks and one of those three is going to be the final workbook, the merged. I made the changes to the workbook that it wanted me to do. I saved those three and closed them and left the merged one open. To merge the workbooks you have the merged workbook open and go to tools and choose merge workbooks. You then choose what workbooks you want merged. To choose a lot at once click one while holding the shift button and click the last one you wish to merge. It selects everything in between. The second project is something I have done before. It wants you to password protect the document. To protect it when you save it hit the tools button then general options and type in the password you wish to have it saved under.

Ch.17--"Working with workbooks and consolidating data"

Chapter 17 explains how to work with multiple worksheets and workbooks. Just as you are able to copy and paste data within a worksheet Excel allows you to move between various worksheets. To copy data betweens worksheets you just copy the data and paste it where you wish. To add more worksheets to a workbook you press and hold down the Ctrl key and put the mouse pointer over one of the tabs and drag it to the right of the last tab and then release the key and the mouse. To rename a tab just double click on it. To print multiple sheets choose the active sheet(s) option on the print menu. A workspace is multiple workbooks and in order to save it you go to file then save workspace. Excel will save all of the workbooks you have up and save them in the workspace.

The first project wants you to create a workbook that has a worksheet for every month of the year. The way to do that is to add more worksheets with the directions above and rename the 12 months the 12 months of the year. The second project is the simplest thing you can do. All you do is open project 17-2a and project 17-2b and save it as a workspace by going to file and save workspace.

The activity wants you to consolidate the data to sum the quarterly totals figures for each department on the Year-End Report. To consolidate the data go to data then consolidate. In the reference section go to the quarter one worksheet and select the totals for each department. And then hit enter, now that data will be consolidated when you hit OK. Do the same for the other three quarters. Go to your Year-End totals and make sure that the first department’s total has the mouse over it and then hit OK. It consolidates the data then adds up the quarterly totals for the departments.

Ch.16--"Templates in Excel and Protection"

Chapter 16 goes over things such as templates and protection. Making a template is rather easy. All you do is create what you wish to be a template. In order to make it a template all you do is when you save the document is in the save as type scroll down to template. And that’s it. It is saved as a template. To bring up the template you open it just like any other document. You are able to protect things in your document. This is helpful if you are working with people and you only want them to change some cells. To unprotect cells you highlight those cells select format and then cells and choose the protection tab. Unclick the locked option. Now those cells are unlocked. If you wish to lock the whole document you can do so by when you save it choose the tools tab then general options. You now have the ability to save the document so that the only way you open it is with a password.

The first project asks you to create a template for the Lazy Dayse Pie Factory. It says to format the text and that is what I ended up doing. I saved the workbook as a template and then I reopened it to make the first quarterly report for the pie factory. The second project is really simple. All you need to do is password-protect the GMRE Real Estate worksheet. I protected it with the password of CAT. I followed the steps that I listed above.

Ch.15--"Importing and Exporting"

In chapter 15 of advanced Excel it talks about how to import and export data from Microsoft Excel. If you want to import something from another program you go into Excel click the Open button and from files of type select what kind of file you wish to import. If you are importing from Word then you would select Text files. That is most likely the kind of import I will use. Then the text import wizard appears and you fill in the options you wish to have, just like any other wizard. Another way to import you go to data menu then import external data then new database query. All you do is select the type of file you wish and then select the database. To export Microsoft Excel information you can simply copy and paste it or you can save it as a Text (Tab delimited) format and then open it in Word.

The first project wants you to import a text file into Excel. I went to open and chose text (tab delimited) in order to see where it is on the drive. Then I just followed the wizard and changed things in the data like the column widths and format. I changed the column widths to see all the data at once and I changed the format to make it more appealing. The activity was to import a text file into Excel. I did the same process as what I used for the first project. To know more about the data I calculated the totals from each month.


The second project wants you to export a file from Excel and to put it into Microsoft Word. I did it the simple way by just copying the data while it was in Excel and just copied it into Microsoft Word. I applied an autoformat to the table so it also looks more appealing. The column widths were fine and I didn’t need to change them.

Ch.14--"Macros, Macros, Macros"

Chapter 14 is all about macros – how to create and use them. To create a new macro, go to the tools menu and then click on macros and select record new macro. For macro name just name the macro the way you wish. In description put what it does and for shortcut key type in a letter a-z and don’t use a key that already has a shortcut. Now click ok and then while the macro is recording do what you want it to do. Whether that is to create a new row or add up a column. Then hit the stop recording button and to use the macro just hit your shortcut key that you assigned earlier. To edit them go to tools macro and macros. When the code pops up you can add or delete what you want. To add something you must type “Selection.Font.Bold = True” is an example.

The first project wants you to create a macro that inserts a column. I just followed the steps I did above except I named it InsrtCol. I ran the macro and it worked. The second project asks you to edit a macro that is already created. I edited it by typing Selection.Font.Bold = True and Selection.Font.Italic = True. The activity wants you to create a macro that totals the columns. I did the same steps as before, but when I was recording it I went under a column and put in the sum formula to total the column. I used the macro to extend it to all columns.

Ch.13--"Pivottables and Pivotcharts"

Chapter 13 is all about another way to organize data. It is called pivottables and pivotcharts. These pivottables let you rearrange and summarize data. To create a pivottable you go to data then pivottable and pivotchart report. Hit next if you want to create a pivottable then select your range of data and finally select where you would like to put the pivottable report. Then it is almost self explanatory after that because the table shows where you would like to drop row fields, column fields and data items. From there you drag your titles to those positions and it creates a pivottable. And if you wish to create a chart of the pivottable, you just click on the chart wizard button on the pivottable toolbar.

The first project wants you to make a pivottable from the data that it gives. So I went to data then pivottable and began making a pivottable. I hit finish and in the row area in the table I dragged Stock Area to it and to the Data area in the table I dragged Cost to it. Now I have a pivottable of Stock Area and Cost.

The second project is another pivottable except this time when I create the table I also put a classification for the page. It asks me to make the row in the table Last Name and the Data in the table to be Grade Point Average. They want the page to be controlled by Class. They only want the senior’s GPA so I go to the top left where class is and click on senior.

This activity wants me to take data, make a pivottable and then make that a pivotchart. A pivotchart will take a pivottable and turn it into a graph that you wish. It utilizes the chart wizard and takes the data from your pivottable. The graph that it wants is a 3-d graph with a title of Total Product Value in Inventory by Stock Area.

Ch.12--"Scenarios with auditing and correlation"

Chapter 12 of Excel involves auditing, data validation, creating scenarios and correlation and regression of data. To use the auditing feature you open the auditing toolbar in the tools menu and select formula auditing. Then you click the trace precedents button to check the precedents of a certain cell. Along with auditing you can use data validation to prevent you from making a mistake later. Data validation will allow numbers to be included in your budget or whatever you are doing, but if a number does not fit the criteria that you enter then it does not let you put it in. With data validation you make an input message to remind you why you can’t put some values in the table. Creating scenarios is exactly how it sounds. You can make a scenario to see where your budget would be at or something like that. To add scenarios you go to the tools bar and find scenarios. Excel also does statistical processes such as correlation and regression. You go to the tools menu and choose data analysis and click whatever you wish to do.

The first project is simple. It asks you to take a financial statement and to create a scenario that could happen. I went to the tools bar and found scenarios. I keyed the range B10:B16 and changed B10 and B11 to create a new scenario. I updated it to the screen.

The second project is to create a correlation for the set of data it gives you. To do a correlation I went to the tools menu and chose data analysis and correlation. I highlighted the cells that I needed to correlate and put that into input range. Then I chose an output range to tell the computer where to place the correlation information. I found out that the correlation for this data was pretty bad so it was not necessary to do the regression.