MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

There is an easy way to extract East, North, and South sales (and data similar to this example) without using text functions. Simply select cells A3:A6, and then on the Data tab of the Ribbon, click Text to Columns. Then select Delimited, click Next, and fill in the dialog box as shown in Figure 5-5 on the next page.

Figure 5-5: Text To Columns Wizard dialog box

Entering the plus sign in the Delimiters area directs Excel to separate each cell into columns, breaking at each occurrence of the plus sign. Note that there are options given for breaking data at tabs, semicolons, commas, or spaces. Now click Next, select the upper-left corner of your destination range (we chose cell A8), and click Finish. The result is shown in Figure 5-6.

Figure 5-6: Result of Text To Columns Wizard

• At the end of each school semester, my students evaluate my teaching performance on a scale from 1 to 7. I know how many students gave me each possible rating score. How can I easily create a bar graph of my teaching evaluation scores?

• The file Repeatedhisto.xlsx contains my teaching evaluation scores (on a scale from 1 through 7). Two people gave me scores of 1, three people gave me scores of 2, and so on. Using the REPT function we can easily create a graph to summarize this data. Simply copy from D4 to D5:D10 the formula =REPT("|",C4). This formula places in column D as many "|" as the entry in column C. Figure 5-7 makes clear the preponderance of good scores (6s and 7s) and the relative rarity of poor scores (1s and 2s). Repeating a character such as | enables us to easily mimic a bar graph or histogram. See Chapter 36, “Summarizing Data with Histograms,” for further discussion of how to create histograms with Excel.

  Figure 5-7: Using the REPT function to create a frequency graph.