ctl.unm.edu unmgr[email protected] (505) 277-1407
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
ctl.unm.edu unmgr[email protected] (505) 277-1407
be visible, even when you scroll to another part of the document or re-arrange the other cells. To freeze
a row or column, go to View > Freeze Panes, and choose the selection you need.
Hiding a Row or Column: You may be working on a large data set and not always need certain details; in
this case it is easiest to Hide them temporarily. Simply select the Row or Column by clicking its letter or
number, then right-click > Hide. When you want to show those later, select around the area where the
range is hidden and right-click > Unhide, or you can also double-click on the small box where the hidden
range is.
Keyboard Shortcuts: Here is a comprehensive list of shortcuts available in Excel:
https://support.microsoft.com/en-us/office/keyboard-shortcuts-in-excel-1798d9d5-842a-42b8-9c99-
9b7213f0040f
Windows vs. Mac Excel: There are several important differences between Excel for Windows and for a
Mac. The Apple version has fewer features in general. For example, it lacks substantial access to Visual
Basic, which means access to Power Query and other advanced functions is limited. It is also unable to
create certain types of Pivot tables and does not have the same kind of database tools available.
However, most of the functions work the same for everyday usage. The major difference is in shortcuts,
where most Apple shortcuts utilize the Command key (
⌘)rather than CTRL. Here’s a handy guide to the
shortcuts between each version:
https://corporatefinanceinstitute.com/resources/excel/shortcuts/excel-shortcuts-pc-mac/
Data Management
Data Cleaning: This is an important step when working with data, especially data you gathered for a
thesis or dissertation. You want to ensure that words are spelled consistently, capitalization is correct,
there are not extra spaces or punctuation, data is formatted the way you need it, and the like. Take
advantage of the straightforward tools within the Review tab, such as Spelling and Translate, as well as
some of the tools here, like Removing Duplicates, Find and Replace, and Cell Formatting. You can also
Clear formatting that you do not need (handy when you are pasting data from separate files into one
file). To do so, go to the Home > Editing > Clear. The drop-down menu gives you further options.
Transposing Data (Row to Column): If you have data in a row or column layout, but wish to transpose it
to arrange the layout differently, you can do so by selecting the range of data, including headers, then
right-clicking at the top-left of the range you wish to insert the cells at, then select Transpose. Most
formulas will automatically shift to match the new layout, but please double-check those with relative or
mixed references.
Removing Duplicates: In many instances when doing research, you want to keep duplicates because
they are still data points—however, at other times duplicates are errors you want to remove or
consolidate. In order to do so, first select a range of cells, then go to the Data > Data Tools > Remove
Duplicates. You can also select the column(s) you want here. Be mindful that this will remove data from
other columns as well, because it determines duplicates based on the range/column you select, then
removes the entire row that is a duplicate.
Importing Data in Excel: If you have an external data set, there are several options for importing it into
Excel. It may import directly if it is a format such as a Comma-Separated Value table (.csv) or delineated
text files that use Tabs to separate cells (.txt), though you will want to Save-As an Excel file. If you
ctl.unm.edu unmgr[email protected] (505) 277-1407
cannot open it directly into Excel, you can instead import it as external data. To do so, you go to Data >
Get Data, then choose your file format from the large variety of options. The most common are going to
be tables in a PDF, a website with an embedded table, or a database file such as through SQL. More
advanced users are able to select Transform Data to work in the Power Query Editor before importing,
but that will not normally be necessary.
Creating a Macro: Macros are shortcuts that you create for yourself. This is helpful when you find that
you are running a repetitive or common operation on your data. For example, you have a long list of
respondents and you need to color-code them in a few different colors, and also bold those names. You
can record a simple macro that bolds and highlights, then just select the cells and press the macro
shortcut. Macros are typically for advanced users, but you can find a lot of the VBA code you’ll need
available online. Please take a look here for a lengthier explanation and some sample codes:
https://exceloffthegrid.com/excel-vba-macros/
Cell Fills
Auto-Filling Cells: Let’s say you want to work on something that requires a long row or column of
numbers. You can type in a few values adjacent to one another, then select them all. From there, go to
the bottom-right corner of the selection, wait for your cursor to become a +, then drag into the cells
you’d like to auto-fill. This will continue whatever pattern you established with the previous numbers.
For instance, if you type in “1, 2, 3” in the first three cells, then drag to auto-fill, it will keep incrementing
by 1 each time (a handy way to number a large list of entries).
Flash Fill: This is a function that will also utilize Excel’s ability to determine a pattern. It works even with
text. For example, if you have a list with last names and an identifying number, and you’d like to have a
column where both are connected, you can type your preferred style into the first two cells of a third
column, then Excel should show a preview as it anticipates what you are doing. If that does not show,
go to Data > Flash Fill. When it shows you the preview, you can click inside the preview box and the cells
will automatically be filled.
Filling a Formula into Adjacent Cells: This is a simple but powerful tip when you need to use a formula
on a large number of cells. Simply input the formula into the left-most or uppermost cell adjacent to
your data (so create a new column or row labeled “TOTAL” for example). From there, make sure it
tabulated correctly, and if it did you can grab the small box in the lower-right corner of the cell and drag
it downwards or rightwards, automatically filling that formula into those cells and having it tabulate the
correct values.
Useful Formulas
Formulas in General: These are where the true power of Excel lies, but they are also among the most
complex tools in Excel. You can insert a formula in one of several ways. First, you can manually type-in
a formula into a cell, then Excel will verify it for you. You can go to the Formula tab and select from the
appropriate category, which is useful if you want to see what formulas exist. And you can also go to the
Formula tab then Insert Function to specify which function you want. There are functions for very basic
and common tasks that this handout will go over, but there are also complex variations that provide
tangent, sine, and cosine, do amortizations, calculate over time, run logic operations, and accomplish
many other things.
ctl.unm.edu unmgr[email protected] (505) 277-1407
Keeping a Cell Reference Constant for Formula: Let’s say you want to have a formula tabulate
something, but it references a constant value as part of the formula. Instead of typing the constant
value into the formula (which requires you to make changes to the formula and all copies of it when you
change the constant) you can instead reference that cell with a $ symbol. So, for example, =SUM
(A3:D3, $A$1) indicates that the formula adds up A3, B3, D3, and A1, with A1 always being the same
value in the formula. There are other types of cell references in Excel, where you use the $ symbol on
one of the reference values but not the other. See here for more info:
https://edu.gcfglobal.org/en/excel2016/relative-and-absolute-cell-references/1/
Reference to Another Sheet: Keep in mind that you can also reference a Cell from a different
worksheet. In order to do so when typing a formula, you can go to another worksheet and then select
the cells you wish to reference. You can also do so manually by typing the name of the worksheet
followed by a ! symbol. For example, =AVERAGE(SHEET2!A2:E2) will give you the average of those cells
in Sheet2. You can also use this to reference multiple different worksheets by placing a comma
between each reference.
If you need to work on several similar worksheets (such as monthly data with the same format but
different values) you can easily do so by writing a range of worksheets, such as in this example:
=AVERAGE(Sheet1:Sheet4!A1:D1) is functionally identical to
=AVERAGE(Sheet1!A1:D1,Sheet2!A1:D1,Sheet3!A1:D1,Sheet4!A1:D1) but the first one uses a range of
worksheets to do so more neatly.
SUM: This formula just adds up a row or column (or other set) of numbers. This is very widely used for
tabulating data, and in order to use it, you just decide where your total will go, select the cell, and type
in =SUM(), including the relevant cells inside the parentheses. Press enter or select the checkmark and
you will see the formula sum up those numbers.
AVERAGE: This formula works just like SUM, except it displays the average of the range of values.
IF Formulas: These are not well-explained to beginners, but they can be very useful. Formulas such as
SUMIF and COUNTIF return values based on their fit to another cell entry. For example, look at the table
below. It calculates the Sum of values in column B for each value of column A. Please note that this
formula also uses constant cell references (noted below) because you cannot simply auto-fill this
formula. If instead you had one entry whose values you needed from a large list, you could simply type
out =SUMIF(A2:A7,A2,B2:B7), which searches through A2-A7 for values identical to A2 and returns the
sum of all adjacent values in B2-B7.
Respondent Value Sum Values for Respondent
A 75 201
B 80 172
A 81 201
C 65 65
B 92 172
A 45 201
COUNTIF is similar but it returns the number of cells where the condition is true, rather than a sum of
the values inside those cells. This is useful for tallying up the total number of matches, for instance, in a
ctl.unm.edu unmgr[email protected] (505) 277-1407
long survey with many respondents, where you may want the number of people who answered a
“maybe” on a specific question. You use the formula by typing =COUNTIF(A1:A7,”maybe”) or could
replace the second value with a cell reference if you prefer. If you had a column of entries with a result
and you wanted to know the percentage from the total, even if they are text strings like “Masters” and
“Doctoral”, you can do that. For example, you could use
=COUNTIF(B2:B15,"Masters")/COUNTA(B2:B15), which counts every Master’s student in column B, then
divides that number by the total count of the column, returning the percentage of Master’s students.
MIN and MAX: These are straightforward, and operate much like other basic formulas. They return the
smallest or largest value from a range.
ROUND, ROUNDUP and ROUNDDOWN: You may have instances where you want to work with a
rounded number, such as when dealing with dollars and cents which do not go below the hundredths
place. These formulas return a rounded value. Note that formatting a cell to only show several decimal
places is not the same as rounding—when you do any operations to that cell Excel will still use the full
value rather than the displayed value.
TEXT: This is another useful formula, that can add text to an entry or range of entries. This is useful if
you need to add a character or prefix/suffix to a range of entries that already exist. It also can be useful
to convert numerical data into plain text that you can then paste directly into something else, like an
email or external program.
PROPER, UPPER, and LOWER: All three of these functions modify the text from a cell to have either
capitalized first letters, all-capital, or all-lowercase letters. This is useful when a sheet comes in one
format, but you need to copy-paste it into documents where case will matter. In order to replace a
column or row with the modified values, insert an adjacent row or column, run the formula against the
original data, then copy it and paste it into the original row or column, taking care to select Values when
you paste (as you will otherwise paste the formula, not the value).
VLOOKUP and XLOOKUP: These formulas search your data set for specific strings or other bits of
information. VLOOKUP is the original, but recent versions of Excel now have XLOOKUP, an improved
formula that can search multiple columns and provides only exact matches by default. These are both
used to look up something in a range or table. In order to use this formula, you type in =XLOOKUP
(value you are searching for, search range, result range). The XLOOKUP will search for a value (or
partial/wildcard value) in the first range, then return the value located in the second range. So, for
example, here’s a small table with last names and ID numbers. If you set the value of one cell to say
“Garza” that lets us find Garza’s ID number by setting range 1 to the first column and range 2 to the
second column, which is displayed in the “1201” below.
Last Name ID Number 1201 Garza
Martinez 1705
Garza 1201
Smith 1304
ctl.unm.edu unmgr[email protected] (505) 277-1407
Tables and Charts
Data Filters: These are a useful way to get to specific data within your larger set. To use a filter, ensure
that you have a row of headers/titles for each column. Go to Data > Filter. You can now click the
header of the column you want to filter, and select the relevant parameters.
Inserting an Excel Sheet into Word: You can have the full functionality of a single Excel sheet in a Word
document, if you need to. Simply go to Word and select Insert > Table> Excel Spreadsheet. Here’s an
example below. If you double-click inside the sample, it will take you to the Excel menu. The Total
column is using a SUM formula, for instance.
Sample 1 Sample 2 Sample 3 Total
10 22 13 45
15 33 23 71
Recommended Charts: Excel now makes it straightforward to turn a table into an attractive chart. Go to
Insert > Recommended Charts, and it will show you a range of possibilities for the data you select. It has
even more options if you then click All Charts. You can use this to make a Line Graph, Pie Chart, Bar
Chart, or a host of other chart formats.
Forecasting: Many of us want to see the trend line for a range of data, and Excel offers the ability to
quickly create a table which extends that trend into future dates (though date is arbitrary, and could be
set to be any other future parameter). In order to do so, select the data you want to forecast, which
should include one column of dates (or other repeated information, such as Sample 1, Sample 2, Sample
3, etc.), and a second column of values, then go to Data > Forecast. Here, you can do What-If Analyses
to adjust data, as well as create a Forecast Sheet. A Forecast Sheet shows what the trend will look like if
it continues, and you can adjust your Confidence Interval. This will create a new sheet showcasing the
trend.
Pivot Tables: Many of us would benefit from learning how to use Pivot Tables. These summarize the
data contained within a larger set. You can go to Insert > Pivot Table. It can also be used to make a
table between multiple groups. The options include the data set, which is typically generated by
default, where to generate the table, and the fields you want it to report. For example, let’s say you
have a table of individual entries, with each entry having the date, location, student year, and Likert
scale assessments for three questions. These would take a lot of time to manage by hand. With a Pivot
Table, you can quickly sort and organize the data, and the table is stored on a separate worksheet. This
way you could sort how many students responded at each location, or how many students responded
per day, or see how many year 2 students took the survey at location A versus location B.
Links to further data analysis cheat sheets: If you would like further support in data analysis, please
review the sheets that we developed, located here under Statistical Analysis:
https://ctl.unm.edu/graduate-students/resources/index.html
