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.
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.
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.