Basics of making professional looking graphs in MS Excel
Graphical data visualization is the key element of almost every research publication. An impressive data reporting can not only helps in clear deductions and convenient result interpretation but also attracts readers’ attention by conveying message in easy-to-understand way.
Recently I came across a document comparing the fundamental periods of 5 buildings (denoted as B1 through B5) computed from actual Eigen-value analysis with the values predicted by an Artificial Neural Network (ANN) model trained on a database of already designed buildings. Here is what the comparison was looking like, when presented in the form of a graph made in MS Excel.
Since there is no relation between any physical parameter of 5 case study buildings, their time periods are scattered data points and hence, showing the comparison in the form of a line graph is not a good idea. Moreover, there is no consistent trend among the two compared quantities for all 5 buildings. Simulated estimations are smaller than actual values for B2, B3 and B5 but not for B1 and B4. The only conclusion which can be drawn from this visual is that predicted values are quite close to actual values and the ANN model is accurate enough.
Now consider reporting the same data in the following form. The “XY Scatter with smooth line” graph is replaced by a “clustered column” type. Following are some additional formatting changes.
- The whole graph text font is changed from default “Calibri (Body)” to “Arial”
- Default gridlines are deleted.
- Tick marks on both axes are added (from axes options).
- The axes lines (both X and Y) are colored full black from axes options (replacing the default grey color)
- The outer grey border of graph is removed (using No Outline option in “Shape Outline” after selecting chart)
- The position of Legend is adjusted by dragging it over the chart area (wherever seem suitable).
- The aspect ratio of graph (ratio of horizontal and vertical dimension) is changed according to column chart type.
- The position of each data label is adjusted by dragging (to avoid default overlapping).
- Add a black line border to “plot area” only.
- Both series (actual and simulated values) are given a distinguishable “shape fill”, independent of what printer will be used by reader to print the document later. In this case, “pattern fill” option is used to fill both series columns with two different black-and-white patterns.
After these formatting changes, the same graph will look something like this.
Although sowing the same data, this chart is relatively more presentable as compared to the previous one. Here is a quick 5 minute video tutorial on formatting charts in MS Excel, uploaded by a YouTube channel named “QIMacros”. Hope you may find it helpful.
Now suppose you want to copy this graph (from excel) to a MS Word document as a picture. An important tip to retain a high quality for printing is to use “Picture (Enhanced Metafile)” in “Paste Special” option while pasting in MS Word. Here is a short tutorial from a YouTube channel named “AuditExcel Advanced Excel and Financial Model Training and Consulting” about various copy and pasting options for Excel Charts in to MS Word.[youtube https://www.youtube.com/watch?v=q-zEcNzY0R0]
An Important option after pasting chart as a picture is to lock its aspect ratio. Right clicking it and selecting “Size and Position” in MS Word will take you to the following form. Checking “Lock Aspect Ratio” will not allow changing one dimension irrespective of other and the picture will not get distorted.
A last important note is about saving an Excel chart or figure as a file. Some formatting software (e.g. Latex) require the figures in the form of picture files to incorporate in final document. MS PowerPoint provides a useful option of “Save as File” for any picture copied to slide. The graph from Excel can be pasted to MS PowerPoint slide using “Paste Special” and “Picture (Enhanced Metafile)” options and then can be saved as picture files by right clicking and selecting second-last option of “Save as File”. Any file format can be selected however “Windows Enhanced Metafile” provides a very high quality “.emf” extension picture file.
An example of generally used options for making publication-quality graphs in Excel can be seen on this video from a YouTube channel named “Dory Video”