Excel Case

Notes

Wk 1

common type of analysis is to show differences between entities. Charts can also help to analyze differences between component parts. Component parts can be any type of breakdown of a whole: specific regions of a company, types of sales items, parts of a product, and so on. If we wish to see a comparison of parts to the whole, we can use bar charts or pie charts to visually analyze the differences. Let's stay with our Help Desk example. The Help Desk has four types of tickets it tracks. When self-help was rolled out, it was rolled out to help users having login problems. Therefore, we may wish to see the difference in proportion of tickets for login problems after we rolled out the self-help functionality. Here's a chart we might create.

Charts to Show Relationships: Some charts, such as scatter charts and radar charts, allow us to see how one set of data relates to another. Let's stay with our Help Desk analysis, only this time we want to see the relationship between customer perception of Help Desk service before and after the rollout of self-help. In order to really understand the impact, we also want to see the change in perception in relation to what users see as most important. We can use a radar chart to see the relationship clearly.

Wk 2

Excel functions have several components--a name (such as IF) and one or more arguments -- or required pieces of information -- which are separated by commas. The IF function works like this:

Name: =IF

Argument one: Condition to be tested

Argument two: What Excel should do if the condition is true

Argument three: What Excel should do if the argument is not true

Add Data Validation. We want to make certain that the user adds correct data. We can use Excel's Data Validation tools to tell the user what kind of data to add--and we can even prevent them from adding incorrect information. In this example, we want to prevent the user from claiming more than 80 hours in a week, since this is against company policy. We can add Data Validation that requires the user to enter a number between 1 and 80 in the Hours input section. If they enter more than 80 hours, we can either warn them or stop them from proceeding:

create a Macro to automate tasks. A Macro is simply a set of steps that we record in Excel. We turn on the Macro recorder, perform the steps, and then turn off the Macro Recorder. If, for example, we want to make it very easy for the user to send their Pay Calculator to their boss, we can turn on the Macro recorder, perform the steps to send the spreadsheet in an e-mail, and then turn off the recorder. We can associate these steps with a button that will allow the user to click on the button and e-mail the spreadsheet.

Wk 3

Before you begin sorting data, it's a good idea to make sure you can get the data back to the original order. It's always smart to back up your file before you make changes, so save your file with a different name. For example, if you have a spreadsheet called Cars.xlsx, save the file under the name, Cars_v2.xlsx. This way, you can always go back to the original file if you need to

Another important tool for analyzing your data is to use subtotals. Suppose we want to find out the average price of all the cars of a certain make or color. Without subtotals, we'd have to sort and filter our data, type in many =AVERAGE( ) formulas, and spend a long time completing our analysis. Instead, we can use Excel's subtotal feature to do the work for us.

An even more powerful tool for analyzing data is the pivot table. Pivot tables allow us to count, summarize, and average data a variety of ways. Because pivot tables give you a lot of flexibility for analyzing data, they can be a little overwhelming to set up for the first time. The best advice is to save your work before you start, and just try experimenting. You can always undo your work if things go wrong. Pivot tables are similar to subtotals in that they are used to summarize data. What makes them so powerful is that they allow you to group data by multiple categories and then create a summary chart. For example, suppose you want to know the total value of your inventory summed up by make and color. To do this, you can create a new table--a pivot table--then grab and summarize your vehicle's average value by make and color.

Wk 4

Office 2010, the ability to move data from Excel into other Office applications is as simple as copying the data you wish to use and pasting it. Because of Excel's ability to track numbers and perform calculations, you will typically use Excel to hold your numerical data. However, when you want to present that data in a request or a proposal, you simply need to copy and paste