Tips and Tricks for Microsoft Excel
Find and Replace: Just like in Word, Excel allows you to Find and Find & Replace within tables. To do so,
simply press CTRL + F or CTRL + H. The Replace function will replace anything it searches for with the
exact text you type into the second box—this is very useful for removing/replacing unwanted
characters, making a mass-edit to change spelling, and the like.
Column Width/Row Height: If you need more space in a column (such as one containing names or
lengthy strings) or if you need to condense a small column, you can adjust the spacing of them by
selecting the letters or numbers at the top or left, then dragging the line separating them to the left or
right (to make them larger or smaller). If you have selected multiple column or row headers, changing
the width or height will adjust them all to be the same spacing.
Cell Formatting: Formatting is not merely a cosmetic exercise—Excel stores data in particular ways, and
knowing which format is necessary is an important thing. However, Excel is also able to auto-format
many types of data, such as inconsistent times that you have entered, once you select the data and
right-click, then go to Format Cells. Most people use the categories, so review that list and see what
suits your needs, whether it is time, currency, or some other format. If you select Time, for example,
there are numerous ways to display the same time, and the same is true for Numbers, where you can
select the places to show beyond the decimal point. Please note that Text-style formatting means any
number in those cells is treated as text, rather than a number that can be manipulated via a formula.
Multiple Worksheets: Beginning Excel users sometimes forget that you can have multiple worksheets.
Each worksheet has its own references, but you can refer to multiple worksheets in a different
worksheet. This is handy when you want to have a worksheet with your original values, a worksheet
with your adjusted values (such as for a normal distribution or with the outliers removed) and another
worksheet with your regressions and other statistical analyses.
Text Wrap: Sometimes you have significant amounts of text in a cell, and you want it to wrap instead of
spilling over to the right. In order to activate Word Wrap, select the cells you want to format, then go to
Home > Alignment > Wrap Text. This will fit the information in the cell to its width.
Merging Cells and CONCATENATE: When you want to Merge cells, keep in mind that it will typically only
show the value from the leftmost cell. To do a simple merge, select the cells, then select Home > Merge
& Center. If you want to merge text information from multiple cells, you’ll instead use the
CONCATENATE formula. That is used by typing in the formula, such as this example
=CONCATENATE(A1,” “, B1). This will combine cell A1 and B1 together, adding a space (the character
within quotation marks) between the two strings.
Locking and Protecting Cells and Sheets: If you want to keep a cell, range of cells, or worksheet locked
(so you don’t accidentally edit them, or if you are sharing with others) you can select the range, then go
to Home > Alignment > Format Cells > Protection, and select Locked. You can also reach this submenu
via a right-click, then selecting Format Cells directly. This will keep them from being edited, but you also
have to Protect the document. In order to do that, you go to the Review menu tab, then select Protect
Sheet or Protect Workbook.
Freezing a Row or Column: Not to be confused with Locking, this is a helpful tool for working on large
spreadsheets. Freezing means that the row or column selected (usually the top or leftmost) will always