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 cells in your table and press Alt+F1 together by using your keyboard as fast as possible. So an instant chart will be shown to you. To apply your intended changes, select the chart and go through the options which is appeared 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, so an instant table with a content of the selected data is displaying to you. In order to change the appearance of this table, again you just need to click on it and find your suitable adjustments from the table tab in the excel ribbon.
Spend Not Much Time to Find Worksheets
Click on 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 a 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 save some suggestions to give you 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, do not afraid of scrolling for some minutes. 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 better to press Shift+Space together.
A Good Summary Data is Available in the Status Bar Without Using any Formulas
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 abandoned you from using such formulas. Also by clicking on this part, you can simply access to 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 cell includes data.
- Ctrl+up arrow – go to the top of the sheet.
- Ctrl+down arrow – go to the bottom of the sheet.
- Ctrl+right arrow – go to the last right side of the sheet.
- Ctrl+left arrow – go to the last left side of the sheet.
Do Tasks Automatically
Not anymore you need to repeat a useful task while you’re working with excel. Be your own developer and create a Macro to introduce 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 on 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 of many Macros later.
With its shortcut on your keyboard, you can do your tasks rapidly.
Click on “Go to special” and Find Your Data Quickly
Since you have entered a large amount of data to excel, it will be so frustrating to find one of them. So this option will help you to catch whatever you want. First select all of your data and follow “Find & Select > Go to Special” directory through the ribbon.
Now a box is appeared which you can choose an element from and click on “OK” to find that specific element.
Define the Role of “Enter”
While pushing “Enter” in excel, it goes down by default 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 a excel file. Follow the directory “Formula Tab > Watch Window”. In the opened box, add a watch and then select the cells which you want to track. By clicking “OK”, some information about each cell will be appeared.
Work with a Combination of Your Worksheets
Imagine having multiple worksheets that you need each of data in one file. Why not combining 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. Now you just need to 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’s content, “format cells” will help us. By this option, many formats for Number, Alignment, Font, Border, Fill and Protection are available. Select any cell and right click to find format cells; also the shortcut Ctrl+1 will show you its window.
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, chose a value for “Allow” option. Each of these values, restrict the cell to show whatever we 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, your message will be shown to the user while the cell is selected by him.
Do not forget to press “OK”.
These are some little tips to have better experience with excel. By knowing more and more tricks in using excel, there would be no hardship in working with it. Although you have some shortcuts to memorize, you have the opportunity to save your time while using excel.
Just try these tips once, by then you’ll turn to an excel master.