Quick Excel Tips
- Display formulas in all cells: Press/hold [Ctrl][~]. Toggles the mode on and off.
- Generate a historgram (frequency distribution) using a countIf formula. link
- Bubble charts, an enhancement to a scatter plot (xy chart) which shows a 3rd dimension in a bubble which is sized based on the 3rd variable. An additional enhancement is to replace the bubble with an image.
Data entry in an Excel spreadsheet can be tedious. Use the power of restricted access to speed up data entry. The idea is that by restricting access to only specific cells (which are for the data), data entry can be quickly completed because the Enter key also serves a dual role by moving the cursor to the next available cell. The "move to the next cell" is down and then to the top of the next column (the first cell).
... more ...
Viewing 2 worksheets side by side
The process to setup viewing 2 worksheets of an Excel workbook at the same time is very simple. Use the following ribbon menu commands:
... more ...
Statistics Functions for Grading
Excel functions can be quite useful for analyzing statistical properties of exams and student grades for a semester.
... more ...
Histogram using a Pivot Table / Chart
Of course it is possible to create a histogram with the Data Analysis Toolkit - but this method provides a different type of control to the process and end result.
- Add a column to the spreadsheet titled "Interval" (or Bin or Range or ...)
- Decide on the interval that you want, i.e. 10's (10, 20, 30) or 5's (10, 15, 20)
- 10's are easy - create a formula =TRUNC(ref to # data,-1), result is a number rounded to 10's
- 5's require another step. Figure out the interval reciprocal ... by dividing 1 by the interval. In this case 1/5 = .2 The next step is similar to that for the 10's except that inside the function we multiple by the interval reciprocal and outside the function we divide by the reciprocal. =TRUNC(ref to # data * interval reciprocal,0) / interval reciprocal
- I used the TRUNC function as I intend to use this for a grade distribution. However, the ROUND function may suit another purpose. And in the beauty of mathematics - you could use other functions such as INT to extract the integer value.
- Create a Pivot Table AND Pivot Chart Report from the data.
- The data range must include the column "Interval" and other relevant data that you may want to see in a drill down Pivot Table (not part of the distribution - but useful for analysis later).
- The Layout for the pivot table is pretty simple:
- drag the "Interval" column onto the Row Fields area
- drag a field (any!) with numeric data onto the data area ... with the default of Count.
- you can clean up the final report if you want - but you now have a decent chart and a pivot table that can be used for further analysis if desired
Histogram with CountIf
Another method of creating a Histogram with a high degree of control is to use the COUNTIF function on calculated intervals. The example shown, allows input of interval size.
- create an IntervalSize named cell and input your desired interval (all of the formulas assume that named ranges are created). 5 is the interval used in AH110
- Create a column for the Interval calculation (starting at AH113 in this case)
- Formula =IntervalSize*INT(AG113/IntervalSize) in English the value in AG113 that you want a histogram of is divided by the Interval Size, using only the integer portion which is multiple by the Interval Size to calculate the interval that the Total falls within. This same formula is used down column AH, which is named HistData
- Set up the Histogram in columns AJ and AK
- Calculate the Interval starting value =MIN(HistData), based on the minimum required by the data.
- Interval formula for AJ114 and the remaining intervals (in this case to 100) =AJ113+IntervalSize
- Calculate the frequency of data points within each of the intervals for AK113=COUNTIF(HistData,AJ113)
- Finished - go ahead and create a simple bar chart as well
Extracting Unique Values from a sorted list
- The old/slow method was to "Sort the list", delete the rows containing the duplicate entries, and then use the resulting "unique" list. Now ....
- Use an array formula to extract only the unique values.
- =IF(COUNTIF($A$1:A1,A1)=1,A1,"") Put this formula in the spreadsheet on the first row of the list.
- As written, your list starts in cell A1 -- if different, change ALL references from A1 to your row/column start.
- Use "Fill Down" (grab the lower right corner of the cell containing the formula) to copy the formula down the column. Obviously, the relative row references will change in the formula to give you each item only once.
- Copy the values from your formula column to a new column (paste values), sort it to eliminate the blank lines and ... you are done! (for another elegant solution that is also array driven check out http://www.cpearson.com/excel/noblanks.htm)
Graphics -- Using Excel Functions for Simple Charts
Creative blog posting with examples of in-cell graphics driven by functions, particularly REPT, which returns a repeated text value a specified number of times.
Another creative Excel resource site
Vertex42 has numerous Excel templates which are very creative. The one that caught my attention today was the timeline using an XY Scatter chart. The result is beautiful! http://www.vertex42.com/ExcelArticles/create-a-timeline.html 2008-09-25
And yet another source of timeline info: Microsoft. Several examples are included - some requiring graphics, others using spreadsheet formatting. 2008-09-25
And another interesting jumble of Excel tips at http://www.aeverett.btinternet.co.uk/tips_excel.htm