How to improve worksheet readability in Excel
How to make your worksheets useful?
Making your sheets easy to read is a fundamental approach of creating useful worksheets. Your message must be crystal clear, a misinterpreted sheet can be devastating. A sheet must be easy to read and follow.
Why create easy to read worksheets?
The above picture is an example of a simple sheet with some random numbers. It is really hard to follow let us say Kiwis monthly numbers to the right. Scrolling and larger columns is also a troublemaker for readers. Sheets printed out on paper has the same problem.
Worksheets must be easy to read in order to be useful.
Table of Contents
1. How to change the zoom level?
Use the zoom in and out buttons located at the bottom right of your Excel window to use your screen area more efficiently.
The "Zoom to Selection" tool located on tab "View" on the ribbon allows you to quickly zoom in to your selected cell range.
The "Zoom to Selection" tool is quicker than trying to use the + and - zoom buttons which are not that granular to make it a perfect fit.
The shortcut keys to zoom to selection is Alt + w + g. First press and release Alt then press and release w and lastly press and release g on your keyboard.
2. How to change the Font size
Increase the font size to make the text easier to read.
- Select the data.
- Go to tab "Home" on the ribbon.
- Press with left mouse button on the font size drop-down list.
- Select a size.
3. Create an indent
Follow these steps to add an indent to all cells on the worksheet:
- Press with left mouse button on the upper left button of your cell grid to select all cells.
- Press with mouse on the indent button on tab "Home" on the ribbon.
This moves all values to the right making the worksheet more visually appealing.
4. How to resize all cell column widths?
The picture below shows columns with variable width based on cell contents.
It is also visually appealing to have the same width of all columns. Follow these steps to make all columns on your worksheet equally large.
- Press with left mouse button on the upper left button of your cell grid to select all cells.
- Press and hold with left mouse button on the column line with the largest width to see it's column width.
The picture below demonstrates the line to press and hold in order to see the width of column A. - Now press and hold on a column line of a smaller column.
- Drag to the right to increase the column width, make it as big as the largest column.
Voila! All columns now have the same width as the largest column on your worksheet.
5. How to lock first row and first column while scrolling?
Excel allows you to keep specific rows and columns visible while the remaining worksheet scrolls. This makes it easier to read huge data tables, you always have the headers visible.
- Go to tab "View" on the ribbon.
- Press with left mouse button on "Freeze Panes" button
- You now have three options:
- Freeze rows and columns based on the selection.
- Freeze top row.
- Freeze the first column.
- Freeze rows and columns based on the selection.
There is a thin line between the frozen column or row and the rest of the worksheet.
You also have the option to convert the data set to an Excel defined table, this will automatically keep the headers visible while scrolling the data. The image above shows you an Excel defined table.
- Press with left mouse button on a cell in the data set you want to convert to an Excel defined table
- Press CTRL + T
- Select checkbox if the table contains headers.
- Press with left mouse button on OK button.
6. How to make two worksheets visible on the same screen?
You can create a new window of the same workbook by pressing the "New Window" button on tab "View" on the ribbon.
The press with left mouse button on the "Arrange All" button to arrange windows. Select check box "Windows of active workbook" to only arrange the current workbook.
- Tiled is good for many different windows.
- Horizontal and Vertical is, in my opinion, best for two worksheets.
- Cascade lets you see all Excel window names in a nice layout.
Use the Split button found on tab "View" on the ribbon to divide the same worksheet into two or more views.
This allows you to scroll each view independently letting you examine, for example, two different datasets, located on the same worksheet, simultaneously.
If you select a cell next to the column letters or row numbers and then press with left mouse button on the "Split" button Excel will divide the worksheet into two windows, see image above.
Any other cell selected will divide the view into four different windows.
Tip! Press and hold on a split line and then drag to change its location.
7. Create a chart
Insert a chart to create a better experience and enhancing the message you want to send.
8. Use sparklines to visualize data
Sparklines is a new tool in Excel 2010, it visualizes data in a single cell in a simplistic form. No x and y-axis only the line.
- Select the cell range containing the data you want to use.
- Go to tab "Insert" on the ribbon.
- Press with left mouse button on "Sparklines" button.
- The data range should now be selected, you will only need to select the location of the spark lines.
- Press with left mouse button on OK button.
9. Highlight every other row
The following article explains how to highlight every other row using Conditional Formatting.
Recommended articles
Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]
10. Highlight selected row
This article explains how to highlight the row or column based on the selected cell.
Recommended articles
Today I would like to share with you these small event handler procedures that make it easier for you to […]
Excel basics category
Table of Contents How to replace part of formula in all cells Substitute multiple text strings - Excel 365 recursive […]
This article demonstrates how to select all blank cells in a given cell range and how to delete them. It […]
What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD […]
Excel categories
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
