Tips and Trick

15 Microsoft Excel Tips and Tricks to Save You Time


Time, Keyboard, and Laptop

Microsoft Excel is popular among professional and everyday users alike. What you may not know is that Excel can do much more than you think. You could be missing out on some great time-saving features and not be aware of it. The following Excel tips and tricks can help you get things done faster.

1. Enter and Format the Current Date or Time

You may already know that you can enter the current date or time on your sheet using a function. But did you know that you can enter the static date or time with a keyboard shortcut? This is a handy way to go if you want a fixed value rather than one that updates each time you open the workbook.

Select a cell and enter one of the following shortcuts:

  • Current date: Ctrl + ; (semicolon) on Windows or Command + ; on Mac
  • Current time: Ctrl + Shift + ; on Windows or Command + Shift + ; on Mac
  • Current date and time: Ctrl + ;, Space, Ctrl + Shift + ; on Windows or Command + ;, Space, Command + Shift + ; on Mac.
Current Date and Time in Excel

Formatting Shortcuts

By default, the date appears as MM/DD/YYYY, but you can change this with a keyboard shortcut as well.

  1. Select the cell where you just entered the date with the shortcut above.
  1. Press and hold Ctrl + Shift on Windows or Command + Shift on Mac.
  1. Press and release # (number sign or hashtag).
  1. Release Ctrl + Shift or Command + Shift.

The date will be formatted as DD-MMM-YY.

Current Date formatted in Excel

Similar to the date, the time is formatted by default. Its format is HH:MM:SS AM/PM. If you want to remove the seconds from the time, you can do so with a shortcut.

  1. Select the cell where you just entered the time with the earlier shortcut.
  1. Press and hold Ctrl + Shift on Windows or Command + Shift on Mac.
  1. Press and release @ (at symbol).
  1. Release Ctrl + Shift or Command + Shift.

The time will be formatted as HH:MM AM/PM.

Current Time formatted in Excel

2. Add a Line Break in a Cell

This is a commonly searched action in Excel. You may want to insert a line break between the data in your cell instead of wrapping it. This is handy if you’re, for example, creating a list in a cell.

Place your cursor after the text where you want to insert the line break, and press Alt + Enter on Windows or Option + Return on Mac to see your line break.

Line Breaks in Excel

Note that you may need to lengthen the row or Formula Bar to see the entire cell contents.

3. Copy Data to Adjacent Cells With Your Cursor

Easily copy a range of cells to the right, left, up, or down using mouse or trackpad gestures. You can also pull up additional options for the copied data.

Copy the Cells

Using the Fill Handle, copy a range of cells without extra key presses or menu commands.

  1. Select the cells you want to copy by dragging your cursor through them.
  1. You’ll see the Fill Handle (plus sign) on the bottom right. Click the Fill Handle and drag to where you want to copy the cells.
Fill Handle dragging to copy

Copy the Cells With Options

If you want to do more with your copied cells, you can pull up more actions quickly.

  1. Select the cells you want to copy to display the fill handle as explained above.
  1. Using your “right mouse button,” drag to where you want the copied cells and release.
  1. You’ll see the options appear in a pop-up box like Copy Cells, Fill Series, Fill Formatting Only, and more. Simply choose the one you want.
Fill Handle dragging to copy and display options

4. Perform Calculations When Pasting Data

A calculation is a common action in Excel, but there’s more than one way to perform one. If you want to quickly add, subtract, multiply, or divide data, you can do so using the Paste Special feature.

As a simple example, we’re using addition. We want to add the values in cells B2 through B6 to those in cells D2 through D6.

Numbers To Add in Excel
  1. Copy cells B2 through B6 using “Copy” on the “Home” tab or by right-clicking and choosing “Copy.”
Values copied in Excel
  1. Select the starting cell containing the values for the calculation. It’s D2 in this example.
Cell to paste data
  1. Choose “Paste -> Paste Special” on the “Home” tab, or right-click and choose “Paste Special.”
Paste Special in the Paste menu
  1. Choose the calculation below “Operation.” In our example, we are selecting “Add.”
Paste Special Add operation
  1. When you click “OK,” you’ll see your cells pasted using the calculation you select, providing you with new values.
Values added with Paste Special in Excel

5. Skip Blank Cells When Pasting Data

When you want to use copy and paste to replace existing data, some of the data may include empty cells. More likely than not, you don’t want to replace actual data with blanks. Using Paste Special once again, paste your data without the empty cells.

As an example, we are updating sales totals for a few salespeople. We want to copy that data to replace the original. Since the copied data contains blank cells, we want to skip pasting those and retain the original data.

Empty Cells in Excel
  1. Copy the cells using your preferred method, select the cell where you want to paste them, and click “Paste -> Paste Special” in the “Home” tab.
Copied cells and Paste Special in the Paste menu
  1. Check the box for “Skip blanks” at the bottom of the Paste Special box, and click “OK.”
Skip Blanks in the Paste Special box
  1. Your pasted data will ignore the empty cells and retain the original data.
Pasted data with blanks skipped

6. Eliminate Blank Cells

Maybe the empty cells in your sheet are already there. Get rid of them faster than manually shifting the cells around in just a few clicks.

  1. Select the column, row, or cell range, press Ctrl + G on Windows or Command + G on Mac to open the “Go To” tool, and click “Special.”
Special in the Go To box
  1. Select “Blanks” and “OK” in the next box.
Blanks in the Go To Special box
  1. Press Ctrl + - (hyphen) on Windows or Command + - on Mac, then select “Shift cells up.”
Shift Cells Up in the Delete box
  1. Select “OK” and your blank cells will vanish.
Eliminated Blanks cells in Excel

Maybe you imported data or mistakenly entered extra spaces during data entry. Whether data or a formula, you can eliminate the unnecessary spaces that could turn into a problem later using the TRIM function.

Select an empty cell, and enter the formula below, replacing the A3 cell reference with your own:

Trim Formula in Excel

Your date will no longer have the extra spaces.

Trimmed Spaces in Excel

Either copy and paste the clean data in the original cell, or remove the original data.

For similar tweaks, look at these additional ways to clean up your Excel data.

One somewhat hidden feature in Excel can drastically improve your data entry. Not only does it keep you from typing data incorrectly, but it saves you from typing it at all. The Pick From Drop-Down List feature uses your current data entries.

We have a list of names in the first column of our sheet. If you start to type one of those names in the cell beneath, Excel provides suggestions on quickly selecting that name again.

Suggested data entry in Excel
  1. Another way to enter one of those names is by right-clicking and choosing “Pick From Drop-Down List.”
Pick From Drop Down List in the Excel shortcut menu
  1. Excel displays a drop-down list of those names, allowing you to choose one.
Drop Down List in Excel

Note that you must use the cell directly beneath the last item in the column to view the drop-down list in Excel.

9. Change Formulas to Static Values

If you’ve finished calculating or analyzing your data with formulas in Excel and want to display the formula results as static values instead, use that trusty Paste Special feature again.

  1. Copy the cell(s) containing the formula(s).
Copy Formula in Excel
  1. Select “Paste -> Paste Special” on the “Home” tab. Note: do not select a different cell or range; keep the cell(s) you copy selected so that you can replace them.
Paste Special in the shortcut menu
  1. In the Paste Special box, choose “Values,” and click “OK.”
Values in the Paste Special box
  1. You’ll see your formula result replace the actual formula with a fixed value.
Static value pasted in Excel

10. Convert Your Rows to Columns and Vice Versa

This is a handy Excel tip if your rows are too long or columns too short, and you want to reverse them. Transform your row into a column or vice versa.

  1. Select and copy the cells that you want to convert.
Copied Cells to Transpose
  1. Click on the cell where you want to place the data, and select “Paste Special” using your preferred method.
  1. When the new window appears, check the box for “Transpose,” and click “OK.”
Transpose in the Paste Special box
  1. Your row will be converted to a column.
Cells Transposed in Excel

For more ways to transpose data in Excel, check out our full tutorial.

11. Transform Text to a Column

This next trick makes it easy to separate the data of a single cell into multiple cells. Instead of manually cutting and pasting or retyping data, convert the cell text to a column.

  1. Select the cell containing the data, go to the “Data” tab, and click “Text to Columns” in the Data Tools group.
Text To Columns on the Data tab

There are three steps to convert your data properly. As you make your selections, you’ll see a preview at the bottom of the box so that you can make a change before moving on, if needed.

  1. Choose the option that fits your current data best from “Delimited” or “Fixed width,” and select “Next.”
Format Step for Text To Columns
  1. Choose this option based on the one you chose in the previous step. For example, if you chose “Delimited,” choose the delimiter.
Delimiter Step for Text To Columns
  1. Choose a “Column Data Format,” and enter or adjust the “Destination.” Click “Finish.”
Data Format Step for Text To Columns
  1. The content of your cell will be spread into separate cells, one per column.
Converted Text To Columns in Excel

12. Save Charts as Templates

If you create a chart in Excel that you want to reuse for different data in your workbook, save it as a template. This allows you to choose that template for the next graph you make, and retain the color scheme and other styles.

  1. Right-click the chart, and choose “Save as Template.”
Save As Template in the Chart menu
  1. In the Save Chart Template box, give it a name, and choose “Save.”
Save Chart Template box
  1. To use your template, select the data, go to the “Insert” tab, and click “Recommended Charts.”
Recommended Charts in Excel
  1. Open the “All Charts” tab, choose “Templates“ on the left, and select your template on the right.
Templates on the All Charts tab
  1. Click “OK” and your customized chart will be ready to go.
Chart from a Template in Excel

13. Use the Quick Analysis Button for Formatting, Charts, and More

Another Excel feature that often goes underused is the Quick Analysis feature. When you work with a group of cells and want to format them, turn them into a chart, or get calculations for them, use the Quick Analysis feature.

  1. Select the cells you want to work with, and click the “Quick Analysis” button on the bottom right of the range.
Quick Analysis Button
  1. Use the tabs in the Quick Analysis window to choose the type of action you’d like to perform.
Quick Analysis Options Excel

The tab options depend on the type of data you select, but normally include Formatting, Charts, Totals, Tables, and Sparklines. Choose one to quickly take care of your data.

14. Perform Actions Quickly With Key Tips

If you use Excel on Windows, take advantage of the Key Tips keyboard shortcuts. Display letters that are assigned to tabs, menu commands, and ribbon buttons. Simply select the letter or combination of letters to perform that action.

  1. Select the Alt key on your keyboard, and the Key Tips will appear as white letters in black squares.
Key Tips in Excel
  1. Choose the letter corresponding to the tab you want to open. In this example, we are pressing H for the “Home” tab.
Home Key Tips
  1. Select the letter for the ribbon button or drop-down menu, then press M to open the “Merge & Center” menu.
Merge Key Tips
  1. Choose the letter you want for the action in the drop-down menu. In this example, we’re pressing C to “Merge & Center” our cell.
Merge and Center Key Tip

Tip: if this is an action you perform often, simply use the key presses in order, just like a keyboard shortcut. Using our example above, press Alt, then H + M + C to perform the merge and center action.

15. Common Excel Keyboard Shortcuts

We have a complete list of keyboard shortcuts for Excel that you can check out. But for some common shortcuts in Windows, here are several that may interest you:

  • Ctrl + –: displays cell deletion options
  • Ctrl + A: selects all of the sheet data
  • Shift + Space bar: selects the active row
  • Ctrl + Space bar: selects the active column
  • Ctrl + C: copies selected data
  • Ctrl + V: pastes copied data
  • Ctrl + Home: selects the first cell with your data
  • Ctrl + End: selects the last cell with data
  • Ctrl + Up arrow: selects the first cell of the active column
  • Ctrl + Down arrow: selects the last cell of the active column

Frequently Asked Questions

How do I change the date and time format in Excel?

For a quick date or time format, select the cell and go to the “Home” tab. Choose the format in the “Number Format” drop-down menu in the Number section.

Alternatively, right-click the cell containing the date or time, and choose “Format Cells.” Go to the “Number” tab, choose “Date” or “Time” to choose the format on the right, and click “OK.”

Why is there no Quick Analysis tool in Excel?

You will not see the button for Quick Analysis if you select a single cell, group of blank cells, a column, or a row in Excel. Be sure to select a cell range or array containing data, and you should see the Quick Analysis button on the bottom right.

How do I AutoSave every minute in Excel?

If you use the AutoSave feature to automatically save your Excel workbook, you can choose how often AutoSave kicks in.

Go to “File -> Options,” and select “Save” on the left. On the right, check the box for “Save AutoRecover information every X minutes,” and enter “1” in the box. Also, check the box beneath for “Keep the last AutoRecovered version if I close without saving.”

Image credit: Pixabay. All screenshots by Sandy Writtenhouse.

Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox

Sandy Writtenhouse

With her BS in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She wanted to help others learn how technology can enrich business and personal lives and has shared her suggestions and how-tos across thousands of articles.


Source link

Leave a Reply

Your email address will not be published. Required fields are marked *