a girl sitting on her bed working with her laptop while drinking from a mug
Share on facebook
Share on twitter
Share on pinterest

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.

Creating instant chart with Alt+F1 shortcut in Excel



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.

Convert Data Cell into a Table by pressing Ctrl+T



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”.

Excels 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”.

File (Excel) > Options (Preferences) > Advanced > Display options for this worksheet > 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:

  1. Choose all cells of the data which you want to analyze.
  2. Press Insert > Recommended Pivot Tables and choose one of them.
  3. From the box in your right hand, you can choose the fields to be added to the report.
Insert > Recommended Pivot Tables in Excel



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.

No Need to Scroll in Order to Select a Large Amount of Data in Columns
No Need to Scroll in Order to Select a Large Amount of Data in Rows

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.

The status bar at the bottom of the Excel


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.

Do Tasks Automatically in Excel: 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.

Do Tasks Automatically in Excel by the Record Macro option



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.

Go To Special Option in Find & Select in Excel

Now a window appears which you can choose an element from, and click “OK” to find that specific element.

select comments in go to especial Window in Excel



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”.

Define the Role of “Enter”: File > Options > Advanced



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.

Track Important Cells with “Watch Window” in the Formula Tab



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”.

“Consolidate” option in Data Tools from Data Tab



Remove Duplicates

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:

  1. You can select each cells that contains duplicate data or select all of them by Ctrl+A
  2. 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”.
Data tab > Remove Duplicates in Excel



Conditional Formatting

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.

Conditional Formatting in Excel


Format 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.

Format Cells option in Excel
Format Cells window in Excel


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
  • Decimal
  • List
  • Date
  • Time
  • Text Length
  • Custom

And there are many conditions to choose for “Data” option:

  • Between
  • 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”.

Add Data Validation to Cells by Data Validation Option in Data tab
input message tab in Data Validation


Trying to Customize your Excel Workbook?

Our Excel experts provide customized solutions to enhance Excel workbooks. We also automate Excel to eliminate repetitive tasks. Please contact us for a free quote.

Subscribe to our Newsletter

Share this post with your friends

Share on facebook
Share on google
Share on twitter
Share on linkedin

Leave a Reply

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