There are numerous ways to enhance your performance when working with Microsoft Excel. By releasing the new version of Excel, actually there would be new features to add to your notes of tips and tricks in using Microsoft Excel.
In this article we will discuss such cunning methods which help you to save your time and also turn you into a spreadsheet master.
Put Your Data in a Chart Swiftly
First select each of the cells in your table and press Alt+F1 together by using your keyboard. And an instant chart will be shown to you. To apply your intended changes, select the chart and go through the options which appear in the ribbon.
Convert Data Cell into a Table in a Flash
Select the data which you want to put in a table and then press Ctrl+T, and an instant table with the content of the selected data is displayed. In order to change the appearance of this table, again you just need to click it and find your suitable adjustments from the table tab in the Excel ribbon.
Spend Less Time to Find Worksheets
Click the “File” menu or maybe “Office Button” depends on which version of Excel you are using. The “Recent” button will guide you to the list of your works which you had recently saved.
Also you are able to customize the number of recent files to see in this section. Do this customization through this direction “Option > Advanced > Display > Show this number of workbooks”.
Hide Data with No Value
In order to have clear data on your spreadsheet, it will be so helpful not to see zero value items. To hide such data, follow this path “File (Excel) > Options (Preferences) > Advanced and uncheck the box of “Show zero in cells that have zero value”.
Fill Cells Swiftly
In its new version, you just need to type your data once and then it will be saved in the suggestion list which will be handy in order to fill the rest of the cells. By pressing enter, you can accept these suggestions.
Apply Your Favorite Format to the Whole Spreadsheet Elements Faster
Any time you decide to format cells, such as adding a color to a group of cells, you are able to do it for all of that group of cells simultaneously.
By using some shortcuts, it will be so time-saving to do this. Here are some examples:
- Format numbers to include two decimal places: Ctrl+Shift+1
- Format in scientific/exponential form: Ctrl+Shift+6
- Format as percentage: Ctrl+Shift+5
- Format as currency: Ctrl+Shift+4
- Format as time: Ctrl+Shift+2
- Format as date: Ctrl+Shift+3
Create Pivot Tables Quickly
To manipulate your data in the way you’d like to and also in order to finish your reporting faster, pivot tables will help you to catch it. This feature enables you to choose the data which you want to work with and do some processes on them.
To create a pivot table follow these steps:
- Choose all cells of the data which you want to analyze.
- Press Insert > Recommended Pivot Tables and choose one of them.
- From the box in your right hand, you can choose the fields to be added to the report.
No Need to Scroll in Order to Select a Large Amount of Data
When you are facing numerous cells and need to select them, there is no need for scrolling. The faster and easier way to select an entire column is to select the first cell in that column and then click on Ctrl+Space together. To perform this for a whole row, you’d be better to press Shift+Space together.
After following the previous tip, just check the status bar at the bottom of the Excel. You will see the data is calculated in many ways and prevents you from using such formulas. Also by clicking this part, you can simply access more features about your selected data.
Use Your Keyboard for Quick Navigation
Here are some shortcuts to allow you act much faster and save your time:
- Ctrl+home – go to the start.
- Ctrl+End – go to the last right bottom cell including data.
- Ctrl+up arrow – go to the top of the range.
- Ctrl+down arrow – go to the bottom of the range.
- Ctrl+right arrow – go to the right end of the range.
- Ctrl+left arrow – go to the left end of the range.
Do Tasks Automatically
Not anymore you need to repeat a repeated task while you’re working with Excel. Be your own developer and create a Macro to define a task and automate it. Go through this directory: File > Options > Customize Ribbon > check the Developer box.
Now a Developer tab is visible at the top of the ribbon. Go through it and click the “Record Macro” button, choose a name and a shortcut for this Macro. Also you can add a description to it not to be confused with many Macros later.
With its shortcut on your keyboard, you can do your tasks rapidly.
Click “Go to special” and Find Your Data Quickly
Since you have entered a large amount of data to Excel, it might be so frustrating to find any of them. To catch your data quickly, first select all your data and follow “Find & Select > Go to Special” directory through the ribbon.
Now a window appears which you can choose an element from, and click “OK” to find that specific element.
Define the Role of “Enter”
While pressing “Enter” in Excel, the next row is selected which is changeable. Go through this path “File > Options > Advanced” and as you see in the picture you can change the role of “Enter”.
Track Important Cells with “Watch Window”
Do not worry about tracking some cells including important data with the mass of information in an Excel file. Follow the directory “Formula Tab > Watch Window”. In the new window, add a watch and then select the cells which you want to track. By clicking “OK”, some information about each cell will appear.
Work with a Combination of Your Worksheets
Imagine having multiple worksheets that you need each of data in one file. Why not combine them? To do this, add a new file to Excel and take a look at “Consolidate” option in Data Tools from Data Tab.
Click on “Consolidate” and in the opened window, browse your files and click “OK”.
Don’t you need duplicate data anymore? There is an option to omit such duplicated elements. Since your whole duplicate data will be permanently deleted by this action, save a backup of your worksheet.
Now follow these steps to remove duplicates:
- You can select each cells that contains duplicate data or select all of them by Ctrl+A
- Go to Data tab > Remove Duplicates and in the opened window, select each column you’d like to remove its duplicate data. Then click “OK”.
With Conditional Formatting option, you are able to apply many formats to your cells. You can find it in the Home tab. Each feature has a function to make some changes to the cells appearance and also to their colors in order to be more distinguishable. Even you can add your own formula to it and have a customized format for cells.
In order to change the appearance of a cell content, “format cells” will help you. By this option, many formats for Number, Alignment, Font, Border, Fill and Protection are available. Select any cell and right click to find the Format Cells option. Or press Ctrl+1, as a shortcut.
Add Data Validation to Cells
Data validation enables you to define the type of data each user is allowed to enter to the cells of a spreadsheet.
Select the cell and then go through this direction “Data > Data Validation”
In the opened window and on the setting tab, choose a value for the “Allow” option. Each of these values, restrict the cell to show whatever you decide. These filtered values are:
- Whole Number
- Text Length
And there are many conditions to choose for “Data” option:
- Not between
- Equal to
- Not equal to
- Greater than
- Less than
- Greater than or equal to
- Less than or equal to
In the “input message” tab, you are able to add a title and a message for your new rule. By selecting the “Show input message when cell is selected” checkbox, the message is displayed upon the cell selection.
Do not forget to press “OK”.