Excel Tips & Tricks

Issue/Question

What are some basic tips for Excel?

Environment

  • Excel

Resolution

Freeze Rows and/or Columns: To avoid having to scroll up, down, and sideways unnecessarily, you can freeze rows at the top of your spreadsheet.  Excel also enables you to freeze columns on the left side of your spreadsheet.   By freezing rows and columns, they’ll be within your view no matter what row or column you’re working in.

Delete Blank Cells: To remove blank cells from a row or column, select the row or column from which you want to remove blanks and press your Control and G keys.  Select “Special” in the Go To dialogue box, click Blanks, and then click Okay.

Spell Check: Just as the numbers you put in your spreadsheet need to be accurate, so do the words you use to label your data.  You can spell check your entire document by pressing the F7 key.  If you only want to check certain rows and columns, highlight them before you press F7.  If your project involves more than one spreadsheet, you can spell check them all at once by grouping them prior to pressing F7.

Combine Text from Different Cells: In certain cases, such as when you’re working with first and last names, you may want to combine text from different cells so that it displays together in a single cell.   For example, if you have a person’s first name in row one of column A and the individual’s surname in row one of column B, you can display the person’s first and last names in column C by using the following formula: =A1&B1.  To put a space between the individual’s first and last names, revise your formula to look like this:  =A1&” “&B1.

Use Text to Columns: For example, if you’re working with first and last names, for instance, and you need to put a person’s first name in one cell and the individual’s last name in a different cell, start by selecting the data cells you want to extract different pieces of text from.  Click on Data, Text to Columns, Delimited, and then Next.  Choose the option you want to use to separate your data under Delimiters or designate one in the box next to Other.  Click Next and then choose the format you want to use and the destination where you want your separated data to appear.  Once you’re done with that, click Finish.

Select Tab Colors: If you’re working with multiple spreadsheets in a workbook, you may have to refer to some of them more often than you look at others.  Color-coding your most-used spreadsheets can help you to get to the information you need faster than you would if all your sheet tabs look alike.  You can color a sheet tab by right-clicking on the tab at the bottom of your screen.  Choose Tab Color from the list that appears and then select the color you want the tab to be shaded.

Synchronize Scrolling: If you’re working with two spreadsheets, you may want to compare a piece of data which can be found in the same location on both spreadsheets.  To get to that information in both spreadsheets at the same time, you can synchronize your scroll.

Transpose Rows and Columns: To transpose data, copy the data you want to reposition and move your cursor to the cell in which you want to start displaying that information.

Auto format height/width: Based upon the data entered, use this feature to auto-align the height and width of cells.

Page setup: Helpful when saving a spreadsheet as a PDF file; Add date, time, page numbers, file location, and sheet name to header and footer of spreadsheet.

Conditional formatting: Something to try when searching for data or duplicate entries.

Gridlines: Also, helpful when saving a spreadsheet as a PDF file, click the checkbox for “Print”.

Details

Article ID: 74381
Created
Mon 3/25/19 11:04 AM
Modified
Fri 11/12/21 10:02 AM