10 Pro Tips for Microsoft Excel
Use MS Excel like a pro with these tips.
Many of you will already be familiar with MS Excel, but for those of you who aren’t, it’s part of the Microsoft Office package and allows users to create spreadsheets, create graphs and organize data.
However most Excel users are only making use of approximately 20% of the program’s functions.
Beginner Tips
We will start with the beginners, these tips should help accelerate your learning process and increase your workflow.
TIP 1: Selecting an entire row or column with keyboard shortcuts
Are you still wasting your time manually selecting rows and columns? Save yourself some time by using CTRL+SPACE to select a column or SHIFT+SPACE to select a row.
TIP 2: Finding and Replacing Data
If you’re someone who is working with large quantities of data, finding what you need can be a struggle. Fortunately CTRL+F will open up a search window allowing you to simply type the word or phrase you are looking for.
Once you locate what you’re looking for via Select or Select All you can go to the Replace tab to make any amendments you desire.
TIP 3: Hiding Data from the worksheet
When dealing with your database you might need to hide some of the data, either because of visual clutter or maybe you need to print it. In order to do this Select the cells you want to hide and then Right Click. From the menu that opens go to Format Cells, this will open a window in the Number tab go to Custom then type “;;;”, click OK and you’re done.
Intermediate Tips
These next tips are aimed at intermediate Excel users with a working knowledge of the basic functions and shortcuts available to them.
TIP 4: Inserting or Deleting entire rows and columns using shortcuts
This tip is another great time saver, adding and deleting rows and columns is one of the most commonly performed tasks when using Excel, saving yourself even a few seconds per action adds up over the course of a day, week and month.
In order to add a new Row(beneath) or Column(right) select a row/column and press CTRL+SHIFT+PLUS(+).
In order to delete a selected row/column press CTRL+SHIFT+MINUS(-).
TIP 5: Autosum Shortcut
There are various methods of calculating the sum of a data range, however if you want to save yourself valuable time and quickly calculate the sum of a range of data simply select the desired range and press ALT + EQUAL(=), that’s all there is to it.
TIP 6: Removing Duplicate Data Points and Sets
Often, when working with big volumes of data, you will find that you come across duplicates. Attempting to manually locate and remove every duplicate entry is extremely strenuous and time consuming. Fortunately there’s a much simpler way to clean up your lists.
In order to remove duplicates you first need to highlight the column or row you would like to remove the duplicates from. The next step is to go to the DATA tab at the top of the page and select “Remove Duplicates” found in the data tools section. This will open a pop-up asking you to confirm the data you are working on, all that’s left is to click OK and you’re done.
TIP 7: Transposing Rows into Columns
There will be times when you will need to transform the data in a row into a new column, copying and pasting each value individually can be very time consuming. In order to save yourself valuable time you can use the transpose feature.
The first thing you need to do is Select the data you wish to transpose.
Next press CTRL+C to copy the data.
After that, right-click on the cell where you would like the transposed data to begin and select the special paste option “Transpose”.
Advanced Tips
The next tips we’ll cover are for people who are very familiar with excel and use it professionally, these are tips on how to better use Excel’s advanced features.
TIP 8: Using Conditional Formatting
Conditional formatting is the best way to highlight specific data across your spreadsheet, whether they’re outlying values, commonalities or anything else that you may be looking for.
In order to do this you first need to select the cell group you wish to format.
The next step is to select “Conditional Formatting” under the HOME tab.
From the drop down menu you will find various options including the ability to create your own rules.
Once you have selected or created a rule that you want click OK to begin formatting the cells.
In order to remove formatting, follow the previous steps but select “Remove Formatting” from the dropdown menu instead.
TIP 9: Maintaining a formula’s values with $
In Excel the purpose of the dollar sign in any given formula is to ensure that the exact values of the formula remain the same irrelevant of its position within the spreadsheet.
By default cell references in Excel formulae are relative. For example if my reference points are A1 and B3 the formula will look like this =SUM(A1,B3). If I now move this formula to a cell 2 columns to the right the formula will be =SUM(C1,D3).
To make this formula absolute we need to insert $ into the formula preceding the values we would like to fix. In this example the formula would look like this =SUM($A$1,$B$3). Now no matter where the formula is moved it will always be calculating the sum of the same cells.
TIP 10: Using the COUNTIF formula to ease your workload
By using Excel’s Countif function, users can easily find how many times a word or number appears within a data set.
To do this we need to use the following formula =COUNTIF(range,criteria).
For example if we want to find out how many times the word CAT appears in a range of data from A1 to C5 the formula would be the following, =COUNTIF(A1:C5,”CAT”).