Final Essay: Evaluation

I studied Excel in high school but I did learn a tremendous amount of new things about Excel 2007 in this class. First, it started with the shortcuts in Excel, then formulas, lookup and database formulas, macros,… I have written on my blog every week what I learnt in Excel. I feel that I have become very familiar with Excel; I don’t have to think too much when I do something in Excel. I liked very much our textbook. It had a lot of useful tools like a retirement calculator for example and many more things. I also liked the CaseGrader because then you can actually see what you are able to do when a case is given to you. I used Excel to set up my academic schedule for next semester. It gave me a good view of what classes are when and if I can take them. I am sure that Excel will also help me in the future, maybe even in the near future like a summer internship. What stands out to me in htis class is that everyone was following with when we were demonstrating something in class. There was a nice collaborative work from everyone and this is thanks to the professor who prepared every class beforehand in an interesting, entertaining, and educational way. I really wanted to join this class because I wanted to master Excel. That is also the reason why I have put a lot of effort in this class. I did all 8 cases and read the textbook. It was an amazing class and I want to thank Professor Jorish for making it the best and most interesting class in YU.

Case 8

Case 8 asks to protect a worksheet. This is useful for a company and other big organizations. A second tool for companies are to create validation rules for data entry. This is like programming. It prevents employees to fenter false data and tells them whats wrong. I like the function of a macro. I heared of macro's but I never knew exactly what it was. I did not know untill I did this case. It actually records your steps and repeats it when you run the macro. I also liked that you can assign a macro to a butto.

Case 7

Case 7 was a compact case with interesting functions like:

❖ Nested IF function
❖ VLOOKUP function
❖ COUNTIF function
❖ SUMIF function
❖ AVERAGEIF function
❖ Database functions

These functions are very useful with large tables. I liked especially the VLOOKUP but even more the database function that will look up for a CRITERIA and then will calculate for what u ask like DSUM, DAVERAGE,...

Nested fuctions and Vlookup

For the case found here we can use nested formulas like =IF(B11>=B6,SUM(C2:C6),IF(B11>=B5,SUM(C2:C5),IF(B11>=B4,SUM(C2:C4),IF(B11>=B3,SUM(C2:C3),IF(B11>=B2,SUM(C2),IF(B11>=0,0,0)))))) . It looks complicated but if you havve a look at it it is not. It just repeats itself. The first part says that if the amount of homeruns is equal or bigger than 60, he will get 1000000 (the sum of C2:C4) and if not it would look if it is bigger orequal to 50 and so on...
For the vlookup you can convert first the range into a table to make it easier. You have to add a third column to the table with the cumulative amounts. After you did that you just need to enter the formule =VLOOKUP(B11,table1,3 where B11 is the lookup data and table1 is the name of the table where it should lookup and 3represents the column of the table where to look ti up and refer to that value.

Case 6

Case 6 was an incredible case. So much stuff in it...amazing. It tests on the editing and formatting of multiple worksheets at the same time. This is very important for when you have similar tables from different affiliates of a company, for example, that have different data but same style of table. By grouping different sheets it is possible to change the formatting of a cell or to enter a formula into all the grouped sheets at the same time. You win so much time by doing it that way. When entering a formule in one sheet it is possible to refer to a cell in a different worksheet. This case also tests on creating a link to an other workbook and also to insert a hiperlink in a cell. At the end it is also asked to save two different workbooks into one workspace. So when you open this particular workspace both workbooks will open together one next to the other (titled). It also asks to create a static web page and a custom template. A custom template is very important in the business world. All companies use templates for letters etc. They always use the same layout, header and footer. So they create one or more custom templates so that they do not have to rework the layout, header and footer every time.

The Pivot table is very useful and it is easy to "play around with it". You can decide what to take as column and row very easy by just dragging it in the boxes of column and row. It is also possible to see what to use as value and so much more...thank you for that class. It was nice ending it like that before pessach with case 6 and Pivot table and chart. Have a nice pessach and congratulation to Professor Jorisch for being elected Professor of the year!

Excel shortcut and function keys

The most used Excel shortcuts are:

• CTRL+1 Displays the Format Cells dialog box.
• CTRL+R Uses the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells to the right.
• CTRL+D Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below.
• CTRL+T Displays the Create Table dialog box.
• F5 Displays the Go To dialog box.
  

---------------------------------------------------------------------------------

The most useful generic Windows shortcuts are:

• CTRL+A Selects the entire data.
• CTRL+C Copies the selected text/object/cells.
• CTRL+V Inserts the contents of the Clipboard at the insertion point and replaces any selection. Available only after you have cut or copied an object, text, or cell contents.
• CTRL+X Cuts the selected text/object/cells.
• CTRL+P Displays the Print dialog box.
• CTRL+Z Uses the Undo command to reverse the last command or to delete the last entry that you typed.
• CTRL+S Saves the active file with its current file name, location, and file format.

For more Excel shortcuts click here or look on the tool at the bottom of the blog.

Case 5

This case is a tuff one. It asks to freeze panes which is very useful for long lists or tables. Second, it tests you on conditional formatting and on filtering data. These help you to get some first information by looking at the data. You are also asked to find subtotals by salesperson. This is asked alot of times in the business world. The last part of this case tests you about a pivot table and pivot chart.  It is a very handy tool but it was hard to find out how to do it. Finally, I did it but I do not really understand what it is for...