After completing the J.P. Morgan Corporate Challenge 5K race I was curious: how many other runners had a similar finish time as I? What an Excel-lent opportunity for a Histogram!
A Histogram analyzes values, groups those numbers into bins, (population frequencies) of your choosing, and displays that data in a table or chart. The Histogram tool is part of the Data Analysis Toolpak. It may not initially appear on your ribbon, but is a cinch to install.
Adding the Data Analysis Toolpak
On the Ribbon, click File, then Options.
On the Manage drop-down, select Excel Add-ins and click Go.
Select Analysis Toolpak and click OK.
For a simple Histogram, here’s what you will need:
Input Range: cells containing values to be reviewed. The range must be sorted in ascending sequence.
Bin Range: cells to act as virtual bins within which Excel will place matching numbers. For example, a teacher grading a test might use Bin values for the test scores she wants to lump together. Bin range is an optional; if left blank, Excel will create Bins.In my example (below) the Bin values are increments of one minute, between 17 and 37 minutes (the fastest and longest finishing times).
Output: Location for the Histogram table. These options include Range, New Worksheet, and New Workbook.
Chart Output: (optional) charts the Histogram table output.
Creating the Histogram Table and Chart
On Data tab of the Ribbon, click Data Analysis. The Data Analysis dialog appears.
Select Histogram and click OK. The Histogram dialog appears.
Select or enter the Input Range (e.g., E11:E2804) , Bin Range (e.g., K13:K32), and Output range (e.g. M12).,
Crowded calendar giving you the blues? Automatically color calendar items by using Conditional Formatting.
Crowded calendar giving you the blues?
Good! Next, toss in some reds and the greens!
You can automatically color calendar items by using Conditional Formatting. Like Categories, conditional formats provide you with a visual method of classifying your calendar items. Once setup these formats go to work automatically, on new and previously entered items. Just assign a color to the condition (aka, criteria) you set, and whammo!Technicolorsplendor!
Using Conditional Formatting to Automatically Color Calendar Entries
View your Outlook Calendar
On the View tab, click View Settings. The Advanced View Settings: Calendar dialog appears.
Click Conditional Formatting. The Conditional Formatting dialog appears.
In the Name area enter a name for the view format.
Click the Color drop-down and select a color.
Click Condition. The Filter dialog appears.
Edit the settings on the Appointments and Meetings, More Choices, and/or Advanced tabs, to create your criteria. For example, to create the FYI filter, enter FYI, INFO, NOTE in the Search for the word(s) area (using commas to separate search strings).
Click OK, OK, and OK to exit dialogs and return to the Calendar screen.