Microsoft Excel is the gold standard in data analysis tools. There’s no question about it – industry experts, professionals and veterans still lean heavily on Excel’s prowess and Swiss Army Knife nature to slice and dice their data.

Excel provides tons of applications and functionalities that we can use according to our own use case. But it can become overwhelming at times, especially if you’re new to the tool. Let me help you out! In this article, we will be discussing some crucial 7 Excel tricks that will make you an efficient business analyst, regardless of the domain you’re coming from.

1) Mastering the SUMIF Function

While the SUM function is probably the most-used function Excel has to offer, and applying it is easy thanks to the AutoSum function, it’s not always the best function for your needs. What if you only want to add up specific cells and disregard the ones you don’t need? If your worksheet is large, selecting the cells manually can be a tedious task. This is where the SUMIF function comes in handy because it only adds up the values that meet criteria specified by you. With the new Formula Builder creating a SUMIF function is easier and quicker than ever before.

Start by selecting the cells you want the calculated totals to appear in, then go to Formulas and click Insert Function.

In the Formula Builder menu on the right side of your screen, select SUMIF and click Insert Function.

You’ll now need to specify the range (which cells you want to add up), as well as the criteria. For example, if you want to add up B1 to B2, specify B1:B2 as the range. If you only want to add up if B1’s value is larger than five, specify >5 and click done.

To do the same for an entire row, select the cell with the SUMIF function, press CTRL + C, select the rest of the column, and click CTRL + V. This will automatically add up columns B and C, provided the values in B are larger than five.

Like all functions created through the Formula Builder, you can always amend the SUMIF function by changing your criteria or clicking in cells you want the function to be applied to.

2) Using ‘Flash Fill’

Auto Fill, an option which fills cells automatically based on information you’ve previously added, has long been a feature of Excel. Since 2013 version Excel added a more sophisticated Fill option: the Flash Fill function. This option can save you a lot of time when you’re entering large amounts of data or if you’re using Excel to compile mailing lists. Like Auto Fill, Flash Fill works based on information you entered previously and detects patterns to help you add new information in a much quicker way. Unfortunately, this option is only available for Microsoft Windows users at this point.

To use Flash Fill go to the Data tab and click the Flash Fill icon. You can also access it by using the CTRL + E keyboard shortcut.

If this feature is enabled it will activate automatically if it detects patterns in your data. If, for example, you already have a list of email addresses and now want to make a list of names, Flash Fill will automatically suggest a full list of names after you’ve started typing. If you are happy with the suggestions press enter to apply.

You can also click and drag the bottom right corner of the cell in which you typed your first entry to fill all the other cells that make up your list.

Finally, if you right click on your first cell and select Flash Fill, other cells that are part of the list will fill automatically.

If you don’t like this feature, you can always switch it off (or switch it back on again if you change your mind). Go to File, Options, Advanced, and tick or untick the Flash Fill box.

Always check data generated through Flash Fill to make sure they are correct. The tool is mostly accurate but may occasionally get confused, especially when extracting names from email addresses.

3) Visualizing Data Without Charts

If you want to maximize the effectiveness of your presentations, convert your data sets into visualizations. This will give your audience, as well as yourself, a quick and easy overview of trends in your data.

If you don’t want to use charts, the Conditional Formatting menu is your ally: It gives you data bars, color scales, and icon sets. Data bars adds an in-cell bar chart to each cell and give cells with the larger values longer bars. Color scales add a mix of colors to each range, whereas icon sets do the same using icons instead of colors.

To add data bars to your cells, select the cells and go to Home, Conditional Formatting, Data Bars, and choose a style you like. You can use the built-in styles Excel offers or use the customize option to create your own theme.

Adding color scales works through the same dialog box, giving you the option to choose between two- or three-tone color scales. The former looks better when printed in black and white.

The same applies to icons: they can be hard to distinguish if printed in black and white. Unlike color scales or data bars, icons respond to the content of the cell. Because numbers tend to be right-aligned, icons are automatically left-aligned, which can cause confusion if they end up too close to the number in the adjoining cell. To change this, select the relevant cells and click on Format on the Home tab. Go to the Alignment tab and change alignment to Justify. This hack will move the numbers closer to the icons to clarify which icons and numbers go together.

4) Forecasting with ‘One Click’

In the past, forecasting data in Excel was a tedious process to be attempted only by users with a good understanding of regression analysis. Excel 2016 has introduced a feature which makes it possible even for complete novices to use Excel for the prediction of trends. With One Click Forecasting it’s now possible to create forecasts with just a few clicks and keystrokes.

Say you want to forecast your sales figures for the next few months based on your performance over the past 12 months. Start by compiling a worksheet with all the data you need.

Click inside your range and click on the Data tab on your ribbon. On the far right you’ll find the Forecast section. Click on the Forecast Sheet option.

You’ll notice immediately that your forecast sheet doesn’t look very sophisticated. Luckily Excel offers plenty of options to customize your sheet and make your forecast more accurate.

One thing you can easily change is the forecast end date. If you decide you’d prefer to forecast trends for the next few years instead of months, change the date in the Forecast End Date box.

You can also set seasonality manually or ask Excel to detect it automatically. Choosing the manual option may give better results, so experiment with both.

Once you’re happy with your forecast click Create.

Note that this feature is only available for Windows users. If you’re using the Mac version of Excel 2016, or an earlier Microsoft version, you can still use the existing forecasting options.

5) Securing Spreadsheets with ‘Protected View’

As every organization should know, online security is more important than ever before. One small mistake or misjudgment can affect an entire company, especially if the result is a virus or data breach. Companies are only safe as long as employees are aware of the importance of online security and apply safety instructions. Excel may not seem like a massive security risk at first sight, but Excel files are frequently shared and downloaded online without a second thought. This is where problems can occur.

To prevent costly mistakes from happening, Excel offers a Protected View which allows users to open and browse any file without the risk of virus infections. Functionality in this mode is limited, with no editing options, but once you’ve decided the document is safe to open you can switch to the Editing View and start working.

Protected View is only available in Excel 2010 and newer versions. If you’re using an older version of Excel you’ll need to decide whether you want to enable macros (taking a potential security risk) without being able to browse the file first.

Excel 2010 and newer versions open workbooks automatically in Protected View if they are downloaded from the internet, saved as a Temporary File, sent as an e-mail attachment, or if they fail validation.

If you want to change your Protected View settings you’ll need to open a document in Protected View and click on the yellow information bar to open the settings dialogue. This will allow you to select which documents you want to mark as trusted.

Changed your mind? Unfortunately you can’t change the status of one workbook, but you can untrust all workbooks by choosing File, Options, Trust Centre, Trust Centre Settings, Clear. Unfortunately this option is not available in Excel 2016 for Mac.

6) Navigate and Manipulate Spreadsheets

If your worksheets are relatively small you probably won’t have trouble navigating them. But what if your company uses worksheets with thousands of entries? If you need to make any modifications, finding or selecting the data you need can be a tedious task. And what if you want to add up large amounts of data in different cells while you lack in-depth knowledge of functions? Luckily, there’s an easy way to do this—it’s all in the keystrokes.

Press CTRL + arrow. This will make Excel jump in the direction of the arrow, which is useful if you want to select an entire row or column. However, you may only need part of a row or column. To make your selection more specific, hold Shift + CTRL and press arrow to select the area you need.

A common modification is adding up rows or columns (for example, to calculate your total sales figure). With a few keystrokes you can create a function to do this for you. First, select all the cells you need using the method described above.

Select an empty row and column using Shift + Right Arrow and Shift + Down Arrow (not using the CTRL key this time).

Next, go to Formulas, click AutoSum, and your selected empty cells will fill automatically with SUM= functions, calculating the total amounts for you.

7) Finding and Replacing More Effectively

Many users know about the Find and Replace option in Excel because it’s included in most Microsoft Office applications. Find and Replace helps you to look up data quickly and replace it with a few keystrokes if needed. However, using this feature in Excel can be surprisingly frustrating and the reason why is not always clear. Even when you’re sure a specific value is part of the worksheet Find and Replace may not be able to detect it. Why is this the case?

Incorrect settings are a common cause of this problem. Unlike applications such as Word, Excel uses different types of data like text, values, and currency. This can cause Find and Replace to overlook the data you’re searching for because it’s incorrectly set for the search you’re trying to perform.

To set this right, follow these steps:

Go to the regular Find and Replace dialogue box.

Change Within Sheet to Workbook if you want to search the entire workbook, rather than one sheet only.

Other ways to amend your search and make it more specific include the option to search By Rows or By Columns, or Match Case for case sensitive data.

Change Look In to Comments if you want to search comments rather than cells. This feature is useful if you use lot of comments in your worksheets to stay on top of changes or important information.