Have you ever wanted to show off your Excel skills to your boss for some praise? Impress them and intimidate your coworkers by learning these 14 Excel tips. Get a step closer to that raise and be your boss’ favorite employee.
Jump to:
- 1. Flash Fill
- 2. Conditional Formatting
- 3. Pivot Tables
- 4. Index and Match
- 5. Use Shortcuts
- 6. Waterfall Chart
- 7. Forecasting
- 8. AutoFill
- 9. Remove Grid Lines
- 10. Password Protection
- 11. Apply Borders
- 12. Excel Ideas
- 13. Goal Seek
- 14. Delete Blank Cells
Impressive Excel Tricks
These Excel tricks are so impressive you will always want to use them.
Let's go:
1. Flash Fill
Using Flash Fill in Excel
Flash Fill allows you to quickly fill out the information in your sheets by using predictive methods. If you built your sheet well, Excel will be able to help you fill out cells with information, sparing precious minutes you’d spend on manually typing everything.
Flash FIll is able to get first and last names, as well as recognize patterns such as locations, item names, and much more. Simply click on the column alongside your data set and type how it should look. Once you’re happy, hit Ctrl + E to flash fill it.
Alternatively, go to the Data tab in your ribbon and select the Flash-fill button.
2. Conditional Formatting
Conditional formatting in Excel
Conditional formatting is a huge visual help when you analyze data sets. It changes a cell’s color based on the data within it and the criteria you set. This can be used to create heat maps, color code common information, and much more. Let’s see how you can start implementing this feature into your spreadsheets.
- Highlight the group of cells you want to work with.
- Make sure to be on the Home tab from the Ribbon, then click on Conditional Formatting.
- Select the logic you want to use from the drop-down menu, or create your own rule to customize how Excel treats each cell.
- A new window should open up where you can provide Excel the necessary rules to sort your data properly. When done, click on the OK button and see your results.
3. Pivot Tables
Excel’s Insert menu
Pivot Tables are extremely useful when analyzing and presenting your data. Select all of the cells you want to use, then create a Pivot Table by going to the Insert tab and clicking Recommended Pivot Tables.
4. Index and Match
(Source: STL)
Deep-dive into your data by using some of Excel’s formulas. After learning how to use the Index and Match feature, you’ll be able to quickly get crucial information out of any data table.
Make sure that your data is formatted in a grid with labels for both headers and rows. Use Match to find the column that contains your search target, then another Match to find a row which contains your answer. Feed these two Match values into an Index for Excel to retrieve the value where the two intersect.
Example:
INDEX ( array, MATCH ( lookup_value , lookup_array, 0 ) , MATCH ( lookup_valite , lookup_array, 0 ) )
5. Use Shortcuts
Excel always gets the credit it deserves for providing a large array of features and capabilities to its users. To truly become an expert in Excel, you need to know about keyboard shortcuts. Here are some of the top, most used Excel shortcuts you need to know in order to level up your Excel game right now:
- Select a column: Click on a cell in a column, then press the Ctrl + Space keys.
- Select a row: Click on a cell in a row, then press the Shift + Space keys.
- Start a new line in a cell: Press Alt + Enter as you type to start a new line.
- Insert current time: Press the Ctrl + Shift + Colon ( ; ) keys.
- Insert current date: Press Ctrl + Colon ( ; ) keys.
- Hide a column: Press the Ctrl + 0 keys.
- Hide a row: Press the Ctrl + 9 keys.
- Show or hide formulas: Press the Ctrl + Tilde ( ~ ) keys.
Check out our The Most Useful Excel Keyboard Shortcuts article to learn more about the wonderful world of shortcuts!
6. Waterfall Chart
(Source: Online Training Hub)
Use waterfall charts to visualize a large variety of data sets. It’s ideal for financial information or statistics — some of our personal favorites are showing net values and stocks. It’s simple and easy to use, all you have to do is go to the Insert menu, select Waterfall or Stock chart, and click on the Waterfall.
7. Forecasting
(Source: Excel Easy)
Get a glimpse into the future by taking advantage of Excel’s forecasting technology. Account for the margin of error to get an outlook for both the best and the worst outcome, then adjust your strategy accordingly to maximize the possibility of success.
Simply go to the Data tab, then select Forecast, and finally, click on Forecast Sheet.
8. AutoFill
Using AutoFill in Excel
Entering a series of repetitive data gets tedious quite quickly. To combat this, use AutoFill. It’s an extremely simple and easy to implement the feature that recognizes repetitive patterns such as dates, and automatically fills in missing cells. Here’s how to use it.
- Select the cells you want to use as a pattern.
- Hover in the bottom-right corner of a cell until the cursor turns into a black + sign. This indicates the AutoFill feature.
- Click and hold your mouse, then drag the cursor down while holding your click. You should see the predicted data appear as you move the mouse.
- Let go of the mouse when you’re satisfied with the entries.
9. Remove Grid Lines
Excel spreadsheet with no grid lines
If you want a completely blank canvas in Excel, you can remove the grid lines from any spreadsheet in one single click. Go to the View tab in the Ribbon, then deselect the Gridlines option in the Show section.
10. Password Protection
Your boss will be certainly impressed by your professionalism if you start protecting data sheets with sensitive information.
Simply go into the File menu, then click on the Protect Workbook box. Here, just select Encrypt with Password and enter the magic word. Now, only people with the correct password will be able to open your workbook.
11. Apply Borders
(Source: Pinterest)
Add a nice visual touch to your Excel spreadsheets by applying borders in an instant. Simply select the cells you want to add a border to, and press the ALT + H + B + A keyboard shortcut.
Alternatively, use ALT + H + B + S to apply a border only to the outside of the cells.
12. Excel Ideas
(Source: Microsoft)
The intelligent Ideas tool will make your workflow a thousand times faster. As Microsoft said: every useful insight begins with a question — don’t be afraid to ask, even if you’re asking a software.
Click on Ideas in the Home tab to get started. Use Excel’s initial ideas, or start asking questions to get precise answers.
13. Goal Seek
In some scenarios, you already know the answer your report is going to show, but you need to have the process behind it showing. Instead of solving the whole equation by yourself, let Excel do the work for you.
Go to the Data tab, then select Data Tools, What-if Analysis, and Goal Seek.
14. Delete Blank Cells
Removing blank cells in Excel
In large spreadsheets, it’s common that some blank cells are left in your workbooks. You don’t have to worry about this and spend hours trying to move everything to the right place to fill those blank spots — Excel can do it for you in seconds.
- Select a column with blank cells in it.
- Switch to the Data tab in the ribbon, and click on the Filter button.
- In the drop-down menu, remove the checkmark next to (Blanks). This should immediately hide all blank cells in the column without messing up your other cells.
- Repeat these steps for any other column which you want to remove blank cells from.
Final thoughts
If you need any further help, don’t be afraid to reach out to our customer service team, available 24/7 to assist you. Return to us for more informative articles all related to productivity and modern day technology!
Would you like to receive promotions, deals, and discounts to get our products for the best price? Don’t forget to subscribe to our newsletter by entering your email address below! Receive the latest technology news in your inbox and be the first to read our tips to become more productive.
Suggested Articles
> 13 Excel Tips and Tricks to Make You Into a Pro
> Top 51 Excel Templates to Boost Your Productivity
> 7 Tips to Become an Excel Mastermind