Microsoft Excel is an essential productivity tool. The ability to easily manage, report on and visualize tables of data is one of Excel’s greatest strengths. Many people, however, find Excel tedious and cannot figure out how it can help them beyond keeping things in neat columns and rows. You can use Microsoft Excel management to analyze large amounts of data or analyze timesheet templates. This post will guide you with the best tips, tricks, and shortcuts one can use to take their Microsoft Excel game to the next level.
Best Microsoft Excel Tips Tricks and Shortcuts for Productivity
These tips can make sure you are on top of your Excel game, regardless of whether you are a beginner or an avid user.
- Change Excel’s Decimal Separators from Periods to Commas
- Get Workbook Statistics
- Use Periods in Dates in Excel
- Change the Cell Reference Style
- Disable Protected View
- View Previous Versions of Excel Workbooks
- Add or Subtract Dates
- Use the Automatic Data Type Tool
- Fill Excel Cells Automatically with Flash Fill
- Insert a Signature Line
1] Change Excel’s Decimal Separators from Periods to Commas
If you’re creating an excel spreadsheet for a person in a country that uses a comma as the decimal separator instead of a period or point, then this might be quite helpful for you. Here’s how you can change Excel’s decimal separator from periods to commas.
- In an Excel spreadsheet, choose the File menu from the ribbon and navigate to Options > Advanced.
- Here, scroll down and find the Use System Separators checkbox under Editing Options. Make sure that the option is unchecked (there is no tick inside the box).
- You’ll notice that the Decimal Separator and Thousands Separator boxes have now become editable. Enter the character you’d like to use for both. You can now change the Decimal Separator from a period to a comma.
- Finally, click on OK to save.
2] Get Workbook Statistics in Microsoft Excel
The Workbook Statistics feature in Microsoft Excel lets you see the number of formulas, tables, or any other measure you’ve taken in a spreadsheet or workbook. Here’s how you can get the workbook statistics in Microsoft Excel.
- In an Excel workbook or a spreadsheet, head over to the Review Tab.
- Now, click on the Workbook Statistics option available under Proofing.
- This will pop open a new small window with all the statistics.
3] Use Periods in Dates in Excel
Microsoft Excel, by default, uses slashes for short dates. However, there’s an option hidden beneath to modify it as you wish. Here’s how you can use periods (or so-called dots) instead of slashes in Excel.
- Start by selecting the cells you’d like to change the formatting of.
- Next, right-click and choose the Format Cells option from the drop-down.
- Now, under the Number Tab in the Format Cells window, select the Category to Custom.
- Here, type in d.yyyy in the Type entry field and click OK.
4] Change the Cell Reference Style in Excel
If you’ve found yourself in a position with numbers on both the rows and columns, you’ve been matched with the R1C1 reference style. Here’s how you can shift back to the much known A1 reference style with alphabets for the columns and numbers for the rows.
- Start by clicking on the File tab present in the ribbon bar.
- Navigate to Options > Formulas from the left navigation pane.
- Uncheck the checkbox for R1C1 reference style present under Working with Formulas.
- Finally, click on OK and accept the changes to continue.
5] Disable Protected View in Excel
Ever downloaded a spreadsheet from an unknown source and been annoyed with the Protected View mode? Here’s how you can switch to the editing mode in Microsoft Excel.
- Head over to the Excel Options window by pressing on the File tab and choosing Options from the left navigation pane.
- Select Trust Center from the vertical tabs and choose the Trust Center Settings
- Here, select the tab for Protected View and disable the options as per your preferences. The options include enabling a protected view for files originating from the Internet, unsafe locations, and Outlook attachments.
6] View Previous Versions of Excel Workbooks
It can take hours to undo an Excel workbook if you accidentally make a few minor mistakes and save it. Here’s where versions step in. You could easily view and restore previous versions of an Excel workbook. Here’s how.
- In an Excel workbook, make sure that the AutoSave toggle at the top left is turned on.
- Next, head over to the File menu and choose the Info tab from the left navigation pane.
- Here, select Version History. With this, you’ll find a new Version History window that will appear on the right. You can click on a version to check its changes.
7] Add or Subtract Dates in Microsoft Excel
Working with dates in Excel? Here’s how you can add or subtract dates in Microsoft Excel.
- You’ll need three columns to go with, the first one containing the original dates, the second one with the number of days to add or subtract with, and the third – final output column.
- Now, enter the number of days you’d like to add or subtract from the dates in the original column. Entering a positive number will add to the date, and a negative number will get subtracted from it.
- Finally, in the third column, enter the following formula: =[Cell1]+[Cell2], where Cell 1 is the original date column, and Cell 2 is the number of days.
8] Use the Automatic Data Type Tool in Microsoft Excel
The automatic data type tool in Excel could be used to add details to your spreadsheets for cities, foods, music, plants, and more. Here’s where you can find it.
- Start by selecting a list of cells you’d like to use the tool on.
- Next, navigate to the Data tab and find the Automatic button present under the Data Types.
- Click on Automatic, and you should notice new icons appearing before the data in the selected cells.
9] Fill Excel Cells Automatically with Flash Fill
Filling in cells is made easier by using Flash Fill, which can automatically recognize patterns in data. What this function does is: for example, if there’s a column with a list of full names, and you’d like to extract the first or the last name, Flash Fill could do the work for you.
- Put the first name from the first cell in the column where you will place the first names.
- Next, head over to the Data Tab, find and select the option for Flash Fill under the Data Tools section.
- With this, Excel will identify the pattern and complete the rest of the list.
10] Insert a Signature Line in Microsoft Excel
Microsoft Excel also offers its users the ability to add a signature to workbooks. Here’s how you can add one too.
- Head over to the Insert tab, and click on the Text option from the right-hand side of the ribbon.
- Next, choose Signature Line > Microsoft Office Signature Line from the drop-down.
- This will pop open a new window with a bunch of options. Fill in all the details as per your preference and click on OK.
Even if you’re not a regular Excel user, you can still use these best Microsoft Excel tips, tricks, and shortcuts to get the most out of Microsoft Excel. That said, do let us know how does Excel help you with your regular tasks by dropping a comment down below.