Excel Tips & Tricks

Freeze Panes

When you have a long spreadsheet, you might have the need to freeze multiple rows so you can still see them at the top when you scroll. Here’s how.

Highlight the rows you wish to freeze & navigate to Window in the top Excel menu and select Freeze Panes.

 

Find/Replace

This tip is very helpful with large volumes of data. For our use case, let’s say you need to find any double spaces in your spreadsheet and replace them with a single space. Here’s how to use the tool:

  • With the Home tab selected, look almost all the way to the far right of the Ribbon and click on Find & Select.

  • In the box that pops up, click on the Replace button in the bottom left corner.
  • In the Find what: field, type two spaces (there are other details in this box that you can use in other use cases).
  • In the Replace with: field, type one space.
  • Next, select Replace All and with one click, you’ve just made a huge edit to your data.

 

Insert rows/columns with a keyboard shortcut

  • Select an entire row or column and hold down Cmd/Ctrl+Shift+Plus(+)

Hide rows/columns with a keyboard shortcut

  • To hide a row, select any cell in that row and press Cmd/Ctrl+9.
  • To hide a column, select any cell in that column and press Cmd/Ctrl+0.
  • To hide multiple columns and/or rows, select multiple cells and press Cmd/Ctrl+9 to hide columns and Ctrl+0 to hide a row.

Add dropdown menus in cells

  • Highlight the cells you wish to insert dropdowns in.
  • Click the Data menu that you find in the top navigation.
  • A Data Validation Settings box will be visible.
  • Consider “Allow options” and tap “Lists.”
  • Select Dropdown List.
  • Press OK after checking the In-Cell dropdown button.

Leave a Reply

Your email address will not be published.