Grafton Falls News

Icon Microsoft Excel

Excel Histogram Charts: A How-To Guide

Excel is a powerful tool for the visual display of data. Leveraging graphs and tables is a useful way to gain a deeper understanding of your data than by just looking at a large data set in its raw form. It is also an effective way to communicate your data to others.

There are a variety of types of graphs (called Charts in Excel) that you can create in Microsoft Excel, including column (or vertical bar) graphs, line graphs, line and point graphs, area graphs, scatter plot graphs, pie graphs, and others.

One special type of graph that you can create in Excel is the histogram, or a vertical bar chart. The histogram is useful for showing count data (quantities of something) across multiple sub-divisions of a category. Displaying counts in a histogram is a form of frequency distribution.

Examples of when to use a histogram include:

a. How many pieces of Halloween candy did each kid in the class collect?

b. How many widgets did each salesperson sell in February?

c. How many days in the month of August did the temperature exceed 100 degrees?

To create Excel histogram charts, follow these steps:

1. Group the data within the category you want to display as a histogram into a table so that there is a unique (summed) quantity per sub-division (a sub-division could be: each kid in the class, each salesperson, each day of the month of August, etc.). For example, if you have a data table that shows that Bob sold 1 widget on February 3 and 2 widgets on February 16, you need to group all of Bob's sales for the month into one line that shows 3 widgets (no need to include the date of the sale, since this information is non-essential for this histogram). You can do this grouping through a combination of sorting and addition, or you can use PivotTables.

2. Your data table should be arranged into two columns: the first column showing each sub-division (again: each kid in the class, each salesperson, etc.) and the other showing the quantity assigned to each sub-division. Make sure each column has a descriptive header in the first row. Note: do not add sub-totals or totals to your table.

3. Sort your table by either the sub-division name or quantity column - your choice.

4. Next, highlight the two columns of data, including the column headers.

5. Click on the Insert tab at the top of your worksheet, then click on the little black down arrow underneath the Column icon in the Charts section.

6. Select the first chart you see, at the top, left of the drop-down menu. Your histogram should then appear on your worksheet.

7. One last step: I suggest adding data labels to your graph to make it easier to read. To do this, just select (click on) any one of the columns in the graph and right click, then select Add Data Labels from the menu.

Histograms are a useful tool for communicating information visually, and with a little practice they are easy to make in Excel.

About the Author

Looking to improve your MS Excel skills? Download your copy of this FREE Excel cheat sheet at:
www.Untangle-Your-Thoughts.com
.

microsoft excel issue. when i open excel files they all appear in the same window?

When i open an excel file it now only opens in the same window? you can only see that i have several excel files when u either minimise each file (and see the file underneath) or on excel go to windows and pick the file. I didnt have this issue before and do not have this issue on microsft word. Issue started today only in excel. Please help it is so annoying. Like to see each excel file on my task bar with everything else open. Thought it was a group similar icon issue but cant find that either, having said that it only is an issue with excel. Could anyone advise a solution? Thank you.

Go to Tools
Options
View
Under Show, click "Windows in Taskbar"

Icon Microsoft Excel
Computer Tech Support : Definition of Icons in Microsoft Excel

Microsoft Excel Data Validation - a Complete Guide to Validating Data in MS Excel

Imagine that you have a spreadsheet asking a client to type in their estimated income into a cell.  You know that this should be in the range from 0 to 1000, but there is nothing to stop the client typing in a negative number, a date or indeed a piece of text.  Until, that is, you apply data validation.

Beginning Validation

In Excel 2003 you can do this by choosing Data from the menu and then choosing Validation; in Excel 2007 and Excel 2010 you do this by clicking on the Data tab of the ribbon, and then choosing Data Validation followed by Data Validation (again).  Whichever method you choose, you will then see a dialog box with three tabs: Settings, Input Message and Error Alert.  Of these, you can ignore the middle Input Message tab (this is used to set a message to appear when you click on a cell, and is better handled with cell comments).

Applying Validation

The first thing to do is to decide what sorf of input is allowed into the cell.  For our example you should choose Decimal from the Allow drop-down list, since this will allow you to input any number (and not just an integer).  You could then specify that you want to allow any number between 0 and 1000.

Displaying an Error Message

Having chosen to allow the right sort of number (in our case, a decimal number between 0 and 1000), it's time to punish a user who transgresses.  To do this, click on the Error Alert tab of the dialog box, and type in a title for the error message to display and the text of it.  Typical values might be:

Title - "Invalid income"

Error message - "You must type in a number between 0 and 1000"

Note that you also have the chance to choose the Style for the validation message, which can be Stop, Warning or Information.  This not only controls which icon will appear next to the message, but also the extent to which invalid values will be allowed.  The safest thing to choose is the default Stop style, which means that it will be impossible to type in any value which doesn't lie in the prescribed range.

Displaying a Drop-List

One special use of validation settings is to restrict a user's choices to those in a drop list.  For example, suppose that you want to allow a user to choose a month from a drop list.  There are clearly 12 possible values, ranging from January to December.  To make this work:

- create a column of values in a spreadsheet (12 cells), containing the months from January to December

- select these 12 cells, and give them a range name (let's say you choose Montths)

- go into the data validation dialog box as described above, and choose to allow values in a List

- in the next part of the dialog box Excel will ask you to specify the Source. Press F3, and choose the Months range name from the dialog box which appears.

You will now see a drop-list when you click in the cell.

Copying Validation Settings

To copy validation settings from one cell to another, select the first cell and copy it, then right-click on the target cell or cells and choose Paste Special.... You can then choose to paste the validation settings only from the dialog box which appears.

Highlighting Cells to Which You've Applied Validation

Not sure which cells you've applied validaiton to?  Press F5 to bring up the GO TO... dialog box and click on the Special... button in the bottom left corner.  You can now choose the appropriate radio button to highlight all cells to which you've applied validation.

About the Author

Andy Brown is a director of Wise Owl Training, a UK company specialising in running Microsoft Excel training courses and other computer training classes. You can try out our free Excel exercises on data validation on our website.

Icon Microsoft Excel