February 10, 2020

15 Excel Tricks To Take Your Business To The Next Level

Very few can claim to have mastered all the intricacies Excel has to offer. However, there are a surprising number of people who only know the basics of spreadsheets. Scratching the surface of advanced Excel features provides extensive opportunities. This isn't only for your personal career growth but can also provide benefits to your small business. Below, we'll review some common tactics to help you utilize Excel for your business.

Excel Is The Swiss Army Knife Of Businesses

Microsoft is useful for far more than just accounting and small-time database storage purposes. There are plenty of business use cases for the seemingly-infinite array of spreadsheets, tabs, and more. It's the ideal, user-friendly flat-file database option. Excel is a wildly useful software option for contact management, CRM (customer relationship management), and beyond. Some even use Excel as their word processor, even though Word is typically packaged right in with Office. However, there's something that most Excel users have in common; they don’t know enough. Unlocking the power of Excel opens up a world of possibilities for your business operations. It’s impossible to learn everything. Even if you do, by the time you’ve got things figured out, Microsoft will release a new version. Books written about Excel become outdated every day. However, it's easy to master some of the most intricate tricks and tips for more efficient user experiences. The goal should be to make various processes more straightforward for you and your business. By doing so, you are getting one step closer to harnessing the real power of Microsoft Excel.

Our Top 15 Excel Tricks

Are you looking to impress your supervisors? Or, maybe you want to take your business data to the next level? If so, the below Excel tips and tricks will go a long way to get you there. From how to make a spreadsheet to impressive conditional formatting, we cover 15 tips to get practical use from Microsoft Excel.

1. Open Excel Files in Bulk

As a business owner, there are times where you need to open multiple Excel sheets at the same time. Maybe you’ve got invoices listed in one sheet that need to be moved over to a main budget sheet. Or, perhaps you have customer information that needs to be copied from multiple files into a new sheet. When this occurs, you won't want to open ten spreadsheets individually. Instead, there's a simplified way to open all of these files with a single click. Just select each file you’d like to open by holding Control (Command on Mac). Then, when they're all highlighted, hit the Enter key.

2. Utilize VLookup

VLookup is one of the most powerful, effective formulas that exists in Excel. It's incredibly useful when you want to find things in ranges or tables by row. One great example of this is finding the prices of parts by part number. Another opportunity is matching employee names to employee IDs. More about how to use the formula can be found here, but the basic gist is simple:
  • The first value is the value you want to look up (the lookup value).
  • Then, you select a range where this value can be located. 
  • Finally, select the column number that contains the value you’d like the return to go in.
The concept of VLookup can be confusing at first. However, harnessing the power of VLookups can take hours of work out of manual data sorting tasks.

3. The One-Click Select All

It’s common knowledge that you can click and drag rows on the left to select multiple rows. It should also be known that you can click and drag columns on the top to choose multiple columns.  However, one thing that isn't as commonly known for Excel practitioners is the option to select an entire sheet. Sure, you can use the Control+A shortcut (Command+A on Mac) to “select all.” However, you can also click the small corner button above row 1 and left of column A to do the same. The One-Click Select All Excel Trick

4. Perform Wildcard Searches

Yes, Control+F (Command+F on Mac) is the shortcut to search sheets. However, two wildcard searches can activate vague search functions. These wildcards, the asterisk and question mark, are ideal for searches that may be broad or unclear. These wildcard searches are ideal when unsure of the target result. A question mark search represents one character unknown. An asterisk denotes one or more characters unknown. More on wildcard searches in Excel spreadsheets can be found here.

5. Generate Unique Column Values

The Filter key function is commonly known for Excel users. However, few utilize the Advanced Filter function. This function is crucial when you want to filter out the unique values of data in columns. To use this feature, select a column, then navigate to data> Advanced. There will be a pop-up. From here, click Copy to move to another location. Then specify a target location by typing the value or clicking a specific area. For example, you could take a list of values in Column A and parse out unique values in Column B. Next, ensure you select Unique Records. It's highly recommended to copy unique data into new columns or sheets. Overwriting the original data may cause referential errors in future parts of a project or workbook.

6. Add Diagonal Lines In Cells

At times, when creating lists, there may be a need to link diagonally. Maybe you're separating different column and row attributes. Or, perhaps you need a way to cross something out without a standard strikethrough. What do you do from here? Home > Font > Borders will get you to the border menu. This is commonly known and allows you to change border width, thickness, color, and more for cells or ranges. However, click More Borders for a surprise opportunity. This includes diagonal lines. Click that and hit OK, and enjoy your new diagonal lines.

7. Use Data Validation

To retain data validity, at times, you might need to restrict what can be input into specific cells. One example of this is having an age column that's constrained to a certain range, say 18 to 65. The best option is to go to data> Data Validation > Settings. Input the necessary conditions and update the message, which will prompt the user if an incorrect value is added. Conversely, there are times you may need to track who makes changes to specific rows of data. Here, make a list of available input names in another column or sheet. Go to where you want the names to be noted. Then, go to data> Data Validation and set it to a range. Highlight your list of names, and hit Enter. The relevant cell will turn into a dropdown. Only the corresponding names will be able to be selected.

8. Try Conditional Formatting

Conditional formatting is one of the best-kept secrets for fast visualization of data. This function assists greatly in visual analysis and exploration of data. You can quickly identify trends and patterns and detect critical problems effectively. With conditional formatting, you can highlight cells or ranges, and emphasize outlier values. You can visualize data with color scales, icon sets, and data bars corresponding to numbers in cells or ranges. Using conditional format changes cell appearance based on specified conditions. For instance, you can get a range of colors from 0 to 100. Or you can have cells turn red for "no" and green for "yes," Excel comes with many inbuilt conditions that can be quickly selected. However, you can easily create your own sets of formatting conditions.

9. Index/Match

Outside of VLookup, the most popular lookup function in Excel is Index/Match. This combination is incredibly flexible and potent. It can be used in a wide variety of areas of spreadsheets. From the most basic to incredibly advanced, Index/Match will get you there. But what makes it unique compared to VLookup? While using VLookup lets you perform lookups with a single function, Index/Match nests two functions. This scares most away, but function combinations provide a massive opportunity for more advanced features. Index/Match is fully covered in this article from ExcelJet.

10. Control/Command Arrows for Fast Movement

Hitting the down button to move row by row down a spreadsheet is inefficient. Scrolling down is a bit more efficient, but still can take forever to shift down large swaths of data.  There's a better way! Click Control (Command on Mac) and any arrow button on the keyword. Then, you can jump to the edge of the sheet in any direction. Instead of clicking each row and scrolling, you'll be taken to the last row of data that isn’t empty. Control/Command Arrows for Fast Movement Excel Trick

11. Learn How to Thoroughly Hide Data

Sure, you can highlight a range of rows, right-click, and select Hide. However, this is only really useful if there is a small amount of data that needs to be hidden. As with everything Excel, there is a more straightforward method to thoroughly hide data: Format Cells. Select a specific area you wish to hide, then click Home > Font > Open Format Cells > Number > Custom. Then, type three semicolons (;;;) and click OK. You'll notice that all values in that area will be invisible. They can only be found in the preview area next to the function. Congratulations, you’ve now thoroughly hidden data in your sheet without hiding entire rows or columns!

12. Use Concatenation To Develop Text Strings

Concatenation is one of the most robust features for combining values in cells visually (not mathematically). It has vast use, from creating unique identifiers to full paragraphs of unique sales copy, concatenate is an incredible feature. One great concatenation example is developing unique identifiers. Let’s say you’ve got column A, listing area codes, column B with last names, and column C with birthdays. Concatenating columns A, B, and C will provide a text string that's area code, last name, and birthday in one cell.  More about concatenation can be found here.

13. The UPPER and LOWER Functions

Sometimes, you have data in cells that are lowercase and uppercase mix, and need them to be one or the other. There are email tools that require all lowercase values for email addresses to be imported correctly. In addition, some databases require all data to be uppercase. Case continuity can rear its ugly head at the worst time. Luckily, Excel has you covered. The formulas =LOWER() and =UPPER() work to put all data in a cell into a lowercase or uppercase format, respectively.

14. Use ‘ To Show Zeros In Values

Go to a brand new Excel sheet and type in 000001. Then hit Enter and watch the zeroes magically disappear! There are times when you need those zeroes. Maybe it’s an invoice number, a product identification code, or even a social security number starting in zero When you need those zeroes, there is a quick workaround that keeps you from needing to change cell formats. When putting your numbers in, add an apostrophe before the number or string of values. This keeps all values, and stops the automatic stripping of leading zeroes.

15. The Wild World of Pivot Tables

Pivot tables could be a massive guide all its own. They are one of the most powerful features in Microsoft Excel. A pivot table lets you extract significant values from extensive data sets. You can boil hundreds of thousands of rows into totals across a small handful of categories.  Learning how to harness pivot tables is one of the most reliable methods to manipulate data effectively. Finding fast conclusions is a breeze with pivots. Find out everything you need to know about pivot tables with this detailed guide from Hubspot.

Are You Ready To Become An Excel Expert?

We hope that these Excel tips have helped open your mind to the power that spreadsheets can have. Whether in your workplace or your place of business, harnessing data is pivotal to success.  If you're looking for additional software tricks for your business, we highly recommend the Fora Financial newsletter. [cta-newsletter]