Histograms in Mac Excel

In this quick post I will demonstrate how to create a histogram in Mac Excel including the Bin Labels as x-axis.

STEP 1: Start with your data formatted as bin and individual frequency (that is derived from the cumulative frequency).

STEP 2: Highlight the entire table, click on Charts, and select Column. You will end up with an incorrect histogram which we'll fix in the next step.

STEP 3: Fix the histogram y-axis values by keeping the correct series (IQ bin).

STEP 4: Fix the histogram x-axis values by copying the IQ bin raw data and then Paste Special inside the Chart (by selecting it before pasting). The data needs to be pasted as a new series with Values Y as Columns, and X Labels as Categories. Make sure you check the box to replace existing categories otherwise it will not work.

STEP 5: You will now end up with the correct histogram.

STEP 6: If you want to widen the bars, right click on one bar, and select "Format Data Series..". In the "Options" tab adjust the "Gap width" to 15%.

STEP 7: Final result.

No comments: