Top 5 Excel Proficiencies For A Young Businessperson

Updated: May 20, 2010

1. Pivot Tables: An easy way to understand and analyze an enormous amount of rows and columns of raw data is via a pivot table. You have a sheet containing hundreds of sales by product ID, prices and sales dates. Your boss wants to know how many of Product ID X were sold on X date. Create a pivot table with Sale Date in the row label and Product ID in values. Place Product ID in the report filter field as well and select the desired product ID for analysis. Note: It is important to make sure each column of data on the raw data sheet contains a unique column header; if you do not have column headers or any of the headers are the same, your pivot will not return accurate data.

2. Simple Mathematical Formulas: Excel is like an elaborate calculator; you will never open another application or use a physical calculator again. It is important that you learn how to do all of the simple mathematical equations you would normally use in business. Excel can instantly give you totals, averages, percentages, etc on almost any volume of data. To get started, enter an = sign in a blank cell and click another number with a numerical value. Enter a + sign and click another cell with a numerical value and press enter. You have now completed your first addition calculation. Repeat these steps for any other type of calculation following general mathematical rules. Note: Read more on calculations such as =sum; =average; for quicker calculations.

3. V Lookup: A reference tool that allows you to search and return notification between two sets of data. You company sends out an email to a list of 50,000 prospects containing their names, email address and phone numbers. 500 contacts open and respond to the email, but the only data you have is their email address. Your boss wants a spreadsheet containing the names, phone numbers and emails of the respondents. Rather than searching email by email, use the V Lookup tool to quickly search your 500 email respondents against your database of 50,000 names and return only the names that match. Note: The two data sets must be on separate sheets within the same workbook; workbook to workbook V Lookups do not work. Use the formula wizard the first few times to learn and walk you through the process.

4. Sort & Filter: An understanding of simple sort and filter principles will save you a lot of time by allowing you to quickly zero in on the data set that you are looking for. Using the example in point #1, you boss now wants to see a sheet of all products that were sold for $X. Go to the price column header and either press ALT + DFF or click sort & filter and select filter. Now simply click the drop down in the price column header, uncheck select all and select only the item (price) you want to reference. You now have a list of all sales filtered by only that specific price. There are also more advanced number filters such as greater than, equals, etc. Sorting data alphabetically or numerically is an additional simple but obligatory skill. When sorting, make sure there are no empty columns before a sort which could jeopardize the sort.

5. Simple Formatting: Once you have learned some of the basic skills, you want your data to look clean and uniform before presenting. Alignment tools such as wrap text and justification should be played around with to ensure the best presentation. I also recommend experimenting with different borders for separating headers from general data or boxing important information. In addition, cell formatting is something to explore for different types of data; rather than wasting keystrokes with $ or % signs, use cell formatting to do the work for you and create uniformity. Once you feel comfortable with basic formatting techniques, move to conditional formatting for advanced and rules based formats.

Featured Research
  • Empty the Airport Lounge

    When it comes to budgeting, travel time and expenses typically make up one of the larger allocations for growing companies. But did you know that there was an alternative that could save you time, money, and spare your employees jet-lag? Businesses that utilize video conferencing see a 30% reduction in travel costs. And while you might think your employees will get upset about the loss of flight status, only 15% are worried about the reduced travel and their frequent flier status. more

  • 10 Reasons to Invest in Video Conferencing

    Have you been on the fence about implementing a video conferencing solution for your business? Not quite sure if your employees will utilize it or are concerned about the costs being too high? The modern workforce is adapting and evolving with more and more employees working remotely, it is essential that they have the tools to be able to communicate effectively. more

  • Checklist for Setting Up an In-House Contact Center

    As customer service continues to become the most important competitor differentiator, can you honestly state that your business ranks among the best in your industry? 93% of organizations expect that contact volume will either remain constant or increase over the next two years. It is absolutely essential that you have a contact center that has been set up for success. more

  • 2017 Business Intelligence Trends

    It's long been thought that business intelligence (BI) could only be utilized by highly trained analysts and was therefore unattainable for most businesses. However, advancements in BI have made it so that everyone can utilize BI solutions to help shape business decisions and drive companies bottom lines. more

  • Your Phone System and Your Bottom Line

    Businesses have been using phones to drive increases to their bottom lines for almost a century now. Telephony, much like the rest of the business world, has seen drastic changes with the increase in technological advancement. Voice Over Internet Protocol (VoIP), has enabled companies to connect with consumers at levels that have been seen as unheard of before. And trust us when we say this, it is doing wonders for the bottom line. more