12 Excel Formulas, Keyboard Shortcuts & Tricks That’ll Save You Lots of Time

This is an abbreviated version of an article by Carly Stec at Hubspot.

1) Quickly select rows, columns, or the whole spreadsheet.

Mac

  • Select Column = Command + Shift + Down/Up 
  • Select Row = Command + Shift + Right/Left 

PC

  • Select Column = Control + Shift + Down/Up 
  • Select Row = Control + Shift + Right/Left 

2) Automatically fill columns or rows with data.

Click and hold the lower right corner of a cell, and then drag it down or across into adjacent cells. When you release, Excel will fill in the adjacent cells with the data from the cell you first selected.

3) Quickly open, close, or create a workbook.

 

For Mac:

  • Open = Command + O
  • Close = Command + W
  • Create New = Command + N

For PC:

  • Open = Control + O
  • Close = Control + F4
  • Create New = Control + N

4) Customize the color of your tabs.

Right click a tab and select “Tab Color.” A popup will appear that allows you to choose a color from an existing theme, or customize one to meet your needs.

5) Format numbers into currency.

Just highlight the cells you wish to reformat and select Control + Shift + $.

The numbers will automatically translate into dollar amounts — complete with dollar signs, commas, and decimal points.

6) Add a comment to a cell.

Right-click the cell you want to comment on and then click Insert Comment. Type your comment in the text box and click outside the comment box to save it.

Cells that contain comments display a small, red triangle in the corner. To view the comment, hover over it.

7) Insert current date and time into a cell.

  • Insert current date = Control + ; (semi-colon)
  • Insert current time = Control + Shift + ; (semi-colon)
  • Insert current date and time = Control + ; (semi-colon), SPACE, and then Control + Shift + ; (semi-colon).

8) Copy and duplicate formatting.

Simply select the thing you’d like to replicate, then select the Format Painter option (paintbrush icon) from the dashboard, as show below:

The pointer will then display a paintbrush, prompting users to select the cell, text, or entire worksheet they’d like to apply that formatting to.

9) Extract specific characters from a cell.

LEFT:

  • Purpose: Used to extract the first X numbers or characters in a cell.
  • Syntax: =LEFT(text, number_of_characters)
  • Parameters:
    • Text. The string that you wish to extract from.
    • Number_of_characters. The number of characters that you wish to extract starting from the left-most character.


For the sake of this example, we entered =LEFT(A2,4) into cell B2, and copied it into B3:B6. This allowed us to extract the first 4 characters of the code.

MID:

  • Purpose: Used to extract characters or numbers in the middle based on position.
  • Syntax: =MID(text, start_position, number_of_characters)
  • Parameters:
    • Text. The string that you wish to extract from.
    • Start_position. The position in the string that you want to begin extracting from. For example, the first position in the string is 1.
    • Number_of_characters. The number of characters that you wish to extract.

 

RIGHT:

  • Purpose: Used to extract the last X numbers or characters in a cell.
  • Syntax: =RIGHT(text, number_of_characters)
  • Parameters:
    • Text. The string that you wish to extract from.
    • Number_of_characters. The number of characters that you want to extract starting from the right-most character.

 

10) Identify duplicate values.

Click into the Conditional Formatting option, and select Highlight Cell Rules > Duplicate Values…

Using the popup, create the desired formatting rule to specify which type of duplicate content you wish to bring forward.

 

11) Add up the sum of cells that meet a certain criteria.

SUMIFS:

  • Purpose: Used to add up cells that meet a certain criteria.
  • Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
  • Parameters:
    • Sum_range.  The range of cells you’re going to add up.
    • Criteria_range1. The range that is being tested using Criteria1.
    • Criteria1. The criteria that determine which cells in Criteria_range1 will be added together.

To see the function in action, check out this example:

In an effort to calculate the sum of the salaries that were greater than $70,000, we used the SUMIF function to add up the dollar amounts that exceeded that number in the cells C3 through C12. To achieve this, we entered =SUMIF(C3:C12,”>70,000″) into the Formula Bar.

12) Clean up irregular spacing.

TRIM:

  • Purpose: Used to remove extra spaces from data (except for single spaces between words).
  • Syntax: =TRIM(“Text”)
  • Parameters:
    • Text. The text from which you want to remove spaces.

Here’s an example of how we used the TRIM function to remove extra spaces after a name on our list: