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.
Formatting Shortcuts
By default, the date appears as MM/DD/YYYY, but you can change this with a keyboard shortcut as well.
- Select the cell where you just entered the date with the shortcut above.
- Press and hold Ctrl + Shift on Windows or Command + Shift on Mac.
- Press and release # (number sign or hashtag).
- Release Ctrl + Shift or Command + Shift.
The date will be formatted as DD-MMM-YY.
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.
- Select the cell where you just entered the time with the earlier shortcut.
- Press and hold Ctrl + Shift on Windows or Command + Shift on Mac.
- Press and release @ (at symbol).
- Release Ctrl + Shift or Command + Shift.
The time will be formatted as HH:MM AM/PM.
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.
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.
- Select the cells you want to copy by dragging your cursor through them.
- 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.
Copy the Cells With Options
If you want to do more with your copied cells, you can pull up more actions quickly.
- Select the cells you want to copy to display the fill handle as explained above.
- Using your “right mouse button,” drag to where you want the copied cells and release.
- 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.
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.
- Copy cells B2 through B6 using “Copy” on the “Home” tab or by right-clicking and choosing “Copy.”
- Select the starting cell containing the values for the calculation. It’s D2 in this example.
- Choose “Paste -> Paste Special” on the “Home” tab, or right-click and choose “Paste Special.”
- Choose the calculation below “Operation.” In our example, we are selecting “Add.”
- When you click “OK,” you’ll see your cells pasted using the calculation you select, providing you with new values.
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.
- 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.
- Check the box for “Skip blanks” at the bottom of the Paste Special box, and click “OK.”
- Your pasted data will ignore the empty cells and retain the original data.
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.
- 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.”
- Select “Blanks” and “OK” in the next box.
- Press Ctrl + - (hyphen) on Windows or Command + - on Mac, then select “Shift cells up.”
- Select “OK” and your blank cells will vanish.
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:
Your date will no longer have the extra spaces.
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.
8. Choose From a Drop-Down List
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.
- Another way to enter one of those names is by right-clicking and choosing “Pick From Drop-Down List.”
- Excel displays a drop-down list of those names, allowing you to choose one.
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.
- Copy the cell(s) containing the formula(s).
- 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.
- In the Paste Special box, choose “Values,” and click “OK.”
- You’ll see your formula result replace the actual formula with a fixed value.
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.
- Select and copy the cells that you want to convert.
- Click on the cell where you want to place the data, and select “Paste Special” using your preferred method.
- When the new window appears, check the box for “Transpose,” and click “OK.”
- Your row will be converted to a column.
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.
- Select the cell containing the data, go to the “Data” tab, and click “Text to Columns” in the Data Tools group.
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.
- Choose the option that fits your current data best from “Delimited” or “Fixed width,” and select “Next.”
- Choose this option based on the one you chose in the previous step. For example, if you chose “Delimited,” choose the delimiter.
- Choose a “Column Data Format,” and enter or adjust the “Destination.” Click “Finish.”
- The content of your cell will be spread into separate cells, one per column.
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.
- Right-click the chart, and choose “Save as Template.”
- In the Save Chart Template box, give it a name, and choose “Save.”
- To use your template, select the data, go to the “Insert” tab, and click “Recommended Charts.”
- Open the “All Charts” tab, choose “Templates“ on the left, and select your template on the right.
- Click “OK” and your customized chart will be ready to go.
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.
- Select the cells you want to work with, and click the “Quick Analysis” button on the bottom right of the range.
- Use the tabs in the Quick Analysis window to choose the type of action you’d like to perform.
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.
- Select the Alt key on your keyboard, and the Key Tips will appear as white letters in black squares.
- Choose the letter corresponding to the tab you want to open. In this example, we are pressing H for the “Home” tab.
- Select the letter for the ribbon button or drop-down menu, then press M to open the “Merge & Center” menu.
- 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.
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
اكتشاف المزيد من موقع شبرون
اشترك للحصول على أحدث التدوينات المرسلة إلى بريدك الإلكتروني.