The more I work with Excel, the more I fall in love with it. Without a doubt, Microsoft Excel has proven itself as the best spreadsheet application that has ever existed. With all its countless and amazing features, more and more new features are being introduced in every new version, making it extra cool.
Are you ready to enjoy these new cool features with my advanced Excel tips?
Well, in this article today, I’ll share some handy tools that I think are cool enough to save you lots of time in your daily life with Excel.
I know pretty well that some of these tricks I am about to share today are not new to some. One thing is also clear, many Excel users are suffering because they don’t know these cool features.
You can do a lot working with Excel, and you can do a lot more if you’re working smart with it. Let’s begin learning it the smart way.
Table of Contents
1. Excel As a Pocket Calculator
Is there ever a need for you to calculate a value before entering it into a cell? It’s time to stop looking for a pocket calculator at such times. Excel can let you enter a formula in a cell, use the result in that same cell, and at the same time get rid of the formula in the background. With this technique, the formula disappears silently leaving only the calculated value in the cell.
How to Use Excel Power Calculator
- Type your formula into the cell: Instead of searching for your pocket calculator to type 893+322, just type =893+322 into the cell where you want the result.
- Hit the F9 key on your keyboard instead of the Enter Key
- Now press the Enter Key to insert the value into the cell
Note that using this technique will replace your formula with the calculated value. Thus, if your calculation is using the values of other cells, Excel won’t update the result if you ever change the value of the other cells.
2. How to capitalize in Excel (Initial caps)
You can change the appearance of text in your spreadsheet with the help of functions like UPPER(), LOWER(), and PROPER(). But the question is, how exactly?
Perhaps you want to capitalize the initials of employees list in an organization. Simply put, you want to change only the first letters of the names to capital letters. What is the trick here?
Well, this is very simple. First of all, you need to know that using a function to automate this task requires you to address it column after column (at least to my knowledge). If the list you want to capitalize is only one column, say the A column, it’ll be a lot easier than if you have so many columns, like about more than 10 columns, because you’ll have to capitalize each column one after the other until they are all exhausted.
Now let’s dive in. Let’s assume that our employee list is in the A column and we want to capitalize the first letter of all the names. Consider the steps and example below:
- Insert a new column to the right (column B in my case)
- Enter the formula =PROPER(A1) in cell B1
- With a relative cell referencing, copy this same formula to all the remaining cells in the B column. (Due to relative referencing, Excel will automatically adjust the formula for all the cells)
At this spot, Excel does its job by displaying properly capitalized names of your employee list in the B column.
Now I have changed the list to the way I want it. Can I then copy the whole of column B to replace Column A? You may ask!
I’m sorry, it won’t work this way because the B column contains formulas that reference the cells you’d be pasting them into, that is column A cells. Thus, rather do the following:
- Select and copy your employee list in column B. (Home➝Clipboard➝Copy)
- Move to Column A (cell A1) and paste as values. (Home➝Clipboard➝Paste➝Values)
To change your text to lower cases or upper cases, use the LOWER() or UPPER() functions respectively. The procedure is the same.
3. How to select only blank cells in Excel
Have you ever find the need to select all blank cells in a range of data? You don’t have to press and hold the Ctrl anymore, you can easily select all blank cells with the help of the Go To tool.
Let’s experiment with the example below:
- Click to select any cell in the range of data that contains the blank cells.
- Press F5 key on your keyboard (A Go To window pops up)
- Select the Special… button.
- Now check the Blanks radio button and click OK.
All blank cells in the range are now selected as seen in the picture below:
Now what? Might you want to replace all the blank cells with some text, I’ll show you how.
Once all the blank cells are selected, the first blank cell will be activated. So just start typing in the word you want to put in all these blank cells, after which press Ctrl+Enter and the word you typed will replace all the blank cells that were selected.
4. Identify your worksheet tabs with colors
When you add a new worksheet, you can rename it for easy identification. However, changing worksheet names aren’t the only way to give identity to sheet tabs. You also have the option to use colors. Its effect on data and printouts is none, and it can help you quickly differentiate between similar tabs.
To change the color of a worksheet tab:
- Right-click the tab (a menu appears)
- Select tab color (different colors appears)
- Make your selection by clicking the color you like (then magic happens)
5. How to print a small portion of a worksheet
At times, you may need to print out only some portion of your data, especially when you are working with large worksheets. Is there a trick that can do that?
Yes of course. You can designate a print area for your worksheet as the only area you want Excel to print. To do that, just follow the following steps:
- Highlight the area you wish to print.
- Select the PAGE LAYOUT
- In the Page Setup group, select Print Area.
- Now select Set Print Area.
After the above steps, you’ll notice that the portion you highlighted now has a thin outline. This means that Excel will only print this portion.
However, only one Print Area can be set at a time. Therefore, setting a new Print Area will, by all means, clear the previous one.
What about if you want to remove your print area to rather print the entire worksheet? Just follow these steps:
- Select the PAGE LAYOUT tab again.
- In the Page Setup group, select Print Area.
- Select Clear Print Area.
6. A very Quick way to chart your data
Using a simple keyboard shortcut, we can create a nice looking bar chart graph with a press of one button (The F11). To experiment this, I will use a table containing 7 months worth of sales figures:
- Using the table above, I will select the data I want to chart. In my case, I’m selecting the entire table:
- Now I will press the F11 key on my keyboard and below will be my results:
A new chart is automatically added to a new worksheet. If you like, you can make further adjustments to the chart by changing the color and layout options.
7. How to edit certain cells on a protected worksheet.
Yes. Sometimes, it is necessary for a worksheet to be protected with some selected cells unprotected so that they can be edited. This is particularly true when you are programming a spreadsheet template to be used by other people. Here’s how to do it:
Let’s assume that the entire worksheet is already protected and the cells you are unlocking are the cells you want people to edit.
To unlock the cells:
- Select the cells you want to be editable (to select multiple cells, hold down the Ctrl key whilst clicking on all the cells you want to select)
- Select Home➝Cells➝Format➝Lock Cell.
Deselecting the “Lock Cell” will unlock all the selected cells.
8. How to find duplicate cells in a worksheet
Is there ever a need for you to find duplicates? If not, you’re going to need that in the future, especially if you are someone who works with Excel a lot, like an accountant or a finance person.
You can find duplicates using the conditional formatting feature and here’s how to do that.:
- Select the range of cells you want to check for the duplicates. If it is the entire worksheet, just press Ctrl + A twice on your keyboard, and if it is only the range that contains data, press Ctrl+A once on your keyboard.
- After the data is selected, Go to Home➝Styles➝Conditional Formatting➝Highlight Cells Rules➝Duplicate Values (As shown below)
Once you’ve done that, all the duplicate values in the worksheet will be highlighted.
What about if you want to remove or delete the duplicate values? Well, you can achieve that by following the steps below:
- Select the range where you want to remove the duplicates
- Now go to Data➝Data tools➝Remove Duplicates (As shown in the picture below):
9. Adjust multiple row heights and column widths at the same time
In Excel spreadsheet, you have full control of row heights and column widths. You just have to right click on the column header (labeled A B C …) or the row header (labeled 1 2 3 …), then choose column width or row height to adjust.
Now the trick here is how to adjust multiple column widths or multiple row heights at the same time. Just look at below steps carefully:
- Select all the columns: Click the column header and drag to the left or to the right to select more columns.
- Now apply a new width, and Excel will use it for all selected columns.
The same trick works for rows.
10. Switching between Absolute, Relative and Mixed cell references.
This handy shortcut is a very simple one that every Excel user should know. To make cells fixed in a formula, pressing the F4 key twice will make the row fixed and pressing the F4 key three times will make the column fixed. It is that simple!
11. How to generate Random Numbers
In fact, this trick helped me a lot when I was asked by a friend to make a hospital database for his project work.
Just like me, at some point, you may need to generate some random figures to save the time of entering data. You can automatically generate such numbers using the RANDBETWEEN function.
- Select the cell in which you want your generated figures to begin.
- Type in the RANDBETWEEN function in that cell. It should be typed like this: RANDBETWEEN(bottom, top)
- The bottom parameter represents the lowest number below which your random figures should not fall and the top parameter represents the highest number above which the random figures should not exceed.
Enter this formula: =RANDBETWEEN(100,2000)
A random number between 100 and 2000 is generated as shown in the picture above.
- Now click and hold the AutoFill Handle and drag it down as many cells as you wish.
The moment you let go of the Fill Handle, you’ll notice that all the cells generated a random number. The RANDBETWEEN function will generate a new figure each time the enter key is pressed and also when you use Undo and Redo.
More tips are loading. So I will entreat you to visit this page in the future to find out my newly discovered Advanced Excel tutorials.