Credit to venkat Krishnan for sharing this

Hi,

Since some of you are new to the org (and it is always good to reinforce for the older ones),
some simple excel good practices to follow in Excel and in Google Sheets are given below.
Do try and follow them. You may want to save this email / star it and keep refering to it when
you work on excel, till these habits become part of you :-). Reasons for each of these
guidelines are given below which u can read if u want to-

1.​ All tabular data must always be created in Excel/Google Sheets. Word is terrible to
manipulate tabular data.
2.​ Always Vertical Align all cells to the TOP (not bottom which is the excel default).
3.​ Use "Wrap text in cell" with small tables (less than 10-15 columns)
4.​ NEVER MERGE CELLS in a working file- u can use merge if the ONLY purpose of
the final design is to print it.
5.​ Don't add a "heading" row to a working sheet- just name the tab with the heading.
Use Heading row if the only purpose is to print.
6.​ Numbers must always be right aligned, text almost always shd be LEFT aligned
7.​ Try your best to avoid "horizontal scrolling" in sheets by reducing the width of
columns and allowing text to wrap in the cell.
8.​ Column Headings must always be shorter length than the text contained in the
column (e.g., use # instead of "Serial number", StDt instead of Start Date, etc.).
Exceptions are when the text in the column is a single character (Y or N, etc.).
9.​ In a table where u have a classification in any column and a subclassification in the
next column, repeat the classification value across all the rows that have the
subclassification​

Category Subcategory Category Subcategory

Animal Donkey Donkey
Animal Cat Animal Cat
Animal Cow NOT> Cow
Bird Pigeon Pigeon
Bird Crow Bird Crow
Bird Mynah Mynah

10.​
11.​Use formulae wherever possible (e.g., while creating an Agenda for a meeting, put
the start time of a session as = cell value of end time of previous session).
12.​Keep a blank row at the end of a table and only then add a total row. The total
formula (sum) must include the blank row value also (e.g., if you have data from B2
to B9, keep B10 blank and in B11, use sum(B2:B10).
13.​Try and use only 1 font colour (black). Highlight cells or rows or columns in
different colours only if the highlight has a specific condition (e.g., all greens are
approved, all yellows are not sure, all reds are reject, etc.).
There are a few more, but this is enuf to start with. And do practice/ read up/ take courses on
Excel shortcuts. You'll be surprised how much your productivity can go up if you learn how to
use them well.

Happy Excel'ing at work ;-)

regards

venkat

The WHY of these guidelines

The WHY of each rule (ignore if too complex)

1.​ Word tables cannot use formulae. Selecting entire rows/columns are not easy,
moving 1 row or column to the top/bottom or right/left is not easy in word, very
easy to do in excel. Overall, much easier to manipulate tables in excel than in
word.
2.​ This is particularly important as sometimes a row will be so tall (e.g., 30 lines of
text) that u can't see the entire row in 1 screen. In such cases, when you scroll left/
right, you won't be able to see the values of other cells which have only 1 line of
entry if they are middle or bottom aligned, but u can always see them when they
are TOP aligned).
3.​ Wrapping text in a cell allows the person to read easily. Otherwise they have to
double-click on each cell to read. However, when you have more than say 200
rows and 30 columns, sometimes it makes sense not to wrap text so that u can get
a "quick summary view" more easily, especially if you've reduced column widths.
Most of you will not deal with such large tables except during recruitment/ eivolve
shortlisting.
4.​ Merged cells are the ENEMY of a "data table". As soon as a cell is merged, the
table can no longer be filtered or sorted. Merging is recommended only when you
need to PRINT something (hard copy or PDF) like Annexures. Even that can be
accomplished using "Headers and Footers" settings.
5.​ A Heading row occupies space without adding any value. In long tables (with
multiple rows) when you want to freeze the row with the column headers, you will
end up freezing the top 2 rows, implying 1 less row of content visible at a time. In
large files, this reduces productivity by 5-8%.
6.​ Numbers shd always be right aligned so that one can visually
compare/add/subtract. In right aligned numbers, it is easier to figure out if the
number is in thousands, millions etc., not so easy with left aligned numbers. With
text on the other hand, since we read from left to right in English, left aligned is
better. "Centre" alignment makes sense only for aesthetic/ printing purposes, and
reduces reading productivity. You will always read MIDDLE aligned text SLOWER
than you will read left aligned text.
7.​ Horizontal scrolling on a laptop is the biggest killer of productivity. You can process
data at 3-5X the speed if there is no horizontal scrolling involved.
8.​ To avoid horiz scroll, we need to reduce col widths. Col widths shd be optimised to
read the content in the table. It is silly to waste pixels (col width) on a header row of
the file.
9.​ Same reason as 4 above- if u merge or put the value only in 1 cell, you lose the
ability to sort/ filter the data.
10.​Whenever we create a file, we almost inevitably edit it. In excel, we may want to
change some values (e.g., in an agenda, change the timings of a session). In the
absence of formulae, a LOT of other values have to be rewritten, whereas if u use
a formula, it automatically recalculates all the other values. While formulae are
easiest to use for numbers and dates/times, they can also be used on text using
string functions like LEFT(), RIGHT(), MID(), &, etc.
11.​Keeping a blank row included in the table before the total row allows you to
sort/filter the table without sorting/filtering the total row. Having a blank row also
ensures that whenever you insert a new row in the data, you don't have to update
the sum formula.
12.​Most of us share files with others. Multiple colours reduce productivity and can be
extremely confusing for other people who have to work with your files unless you
have explained what the purpose of those colours are.