5 Easy Excel Shortcuts Every Business Owner Should Know

5 Easy Excel Shortcuts Every Business Owners Should Know

Microsoft excel has become an essential tool for small and medium size businesses in recent times. Recording daily transactions in journals other vital information is a good culture. But the benefits of using an excel sheet to record business transactions will enable businesses to digitalis data for further analysis. We present you 5 excel shortcuts to help you be more efficient at using a spreadsheet to record business information (data).

Truth be told, data is the backbone of every business and excel will do alot for your data. If your business fails to keep data then get it clear, your planning to fail. Mastering excel shortcuts will save your business time and money. Shortcuts will definitely assist you to replicate columns. Advanced formulas in Excel can turn manual processes that took weeks to complete in the 1980s into something that takes only a few minutes today.

Trust me, after you meticulously read through these 5 easy excel shortcuts, you will eventuality become proficient and effective when using excel spreadsheets. These shortcuts below will help your business thrive.

Excel Formulas

1) SUMIF

Let’s say you want to determine the profit you generated from a list of leads who are associated with specific area codes, or calculate the sum of certain employees’ salaries — but only if they fall above a particular amount. Doing that manually sounds a bit time-consuming, to say the least.

With the SUMIF function, it doesn’t have to be — you can easily add up the sum of cells that meet certain criteria, like in the salary example above.

  • The formula: =SUMIF(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
    • Sum_range: The range of cells you’re going to add up.
    • Criteria_range1: The range that is being tested using Criteria1.
    • Criteria1: The criteria that determine which cells in Criteria_range1 will be added together.

In the example below, we wanted to calculate the sum of the salaries that were greater than $70,000. The SUMIF function added up the dollar amounts that exceeded that number in the cells C3 through C12, with the formula =SUMIF(C3: C12,”>70,000″)

 

2) TRIM

Email and file sharing are wonderful tools in today’s workplace. That is, until one of your colleagues sends you a worksheet with some really funky spacing. Not only can those rogue spaces make it difficult to search for data, but they also affect the results when you try to add up columns of numbers.

Rather than painstakingly removing and adding spaces as needed, you can clean up any irregular spacing using the TRIM function, which is used to remove extra spaces from data (except for single spaces between words).

  • The formula: =TRIM(“Text”)
    • Text: The text from which you want to remove spaces.

Here’s an example of how we used the TRIM function to remove extra spaces after a name on our list. To do so, we entered =TRIM(“Steve Peterson”) into the Formula Bar.

 

3) LEFT, MID, and RIGHT

Let’s say you have a line of text within a cell that you want to break down into a few different segments. Rather than manually retyping each piece of the code into its respective column, users can leverage a series of string functions to deconstruct the sequence as needed: LEFT, MID, or RIGHT.

LEFT:

  • Purpose: Used to extract the first X numbers or characters in a cell.
  • The formula: =LEFT(text, number_of_characters)
    • Text: The string that you wish to extract from.
    • Number_of_characters: The number of characters that you wish to extract starting from the left-most character.

In the example below, we entered =LEFT(A2,4) into cell B2, and copied it into B3:B6. That allowed us to extract the first 4 characters of the code.

MID:

  • Purpose: Used to extract characters or numbers in the middle based on position.
  • The formula: =MID(text, start_position, number_of_characters)
    • Text: The string that you wish to extract from.
    • Start_position: The position in the string that you want to begin extracting from. For example, the first position in the string is 1.
    • Number_of_characters: The number of characters that you wish to extract.

In this example, we entered =MID(A2,5,2) into cell B2, and copied it into B3:B6. That allowed us to extract the two numbers starting in the fifth position of the code.

RIGHT:

  • Purpose: Used to extract the last X numbers or characters in a cell.
  • The formula: =RIGHT(text, number_of_characters)
    • Text: The string that you wish to extract from.
    • Number_of_characters: The number of characters that you want to extract starting from the right-most character.

For the sake of this example, we entered =RIGHT(A2,2) into cell B2, and copied it into B3:B6. That allowed us to extract the last two numbers of the code.

4) VLOOKUP

This one is an oldie, but a goodie — and it’s a bit more in depth than some of the other formulas we’ve listed here. But it’s especially helpful for those times when you have two sets of data on two different spreadsheets, and want to combine them into a single spreadsheet.

My colleague, Rachel Sprung — whose “How to Use Excel” tutorial is a must-read for anyone who wants to learn — uses a list of names, email addresses, and companies as an example. If you have a list of people’s names next to their email addresses in one spreadsheet, and a list of those same people’s email addresses next to their company names in the other, but you want the names, email addresses, and company names of those people to appear in one place — that’s where VLOOKUP comes in.

Note: When using this formula, you must be certain that at least one column appears identically in both spreadsheets. Scour your data sets to make sure the column of data you’re using to combine your information is exactly the same, including no extra spaces.

  • The formula: VLOOKUP(lookup value, table array, column number, [range lookup])
    • Lookup Value: The identical value you have in both spreadsheets. Choose the first value in your first spreadsheet. In Sprung’s example that follows, this means the first email address on the list, or cell 2 (C2).
    • Table Array: The range of columns on Sheet 2 you’re going to pull your data from, including the column of data identical to your lookup value (in our example, email addresses) in Sheet 1 as well as the column of data you’re trying to copy to Sheet 1. In our example, this is “Sheet2!A:B.” “A” means Column A in Sheet 2, which is the column in Sheet 2 where the data identical to our lookup value (email) in Sheet 1 is listed. The “B” means Column B, which contains the information that’s only available in Sheet 2 that you want to translate to Sheet 1.
    • Column Number: The table array tells Excel where (which column) the new data you want to copy to Sheet 1 is located. In our example, this would be the “House” column, the second one in our table array, making it column number 2.
    • Range Lookup: Use FALSE to ensure you pull in only exact value matches.
  • The formula with variables from Sprung’s example below:=VLOOKUP(C2,Sheet2!A:B,2,FALSE)

In this example, Sheet 1 and Sheet 2 contain lists describing different information about the same people, and the common thread between the two is their email addresses. Let’s say we want to combine both datasets so that all the house information from Sheet 2 translates over to Sheet 1.

 

5) IF

There are times when we want to know how many times a value appears in our spreadsheets. But there are also those times when we want to find the cells that contain those values, and input specific data next to it.

We’ll go back to Sprung’s example for this one. If we want to award 10 points to everyone who belongs in the Gryffindor house, instead of manually typing in 10’s next to each Gryffindor student’s name, we’ll use the IF-THEN formula to say: If the student is in Gryffindor, then he or she should get ten points.

  • The formula: IF(logical_test, value_if_true, value of false)
    • Logical_Test: The logical test is the “IF” part of the statement. In this case, the logic is D2=”Gryffindor.” Make sure your Logical_Test value is in quotation marks.
    • Value_if_True: If the value is true — that is, if the student lives in Gryffindor — this value is the one that we want to be displayed. In this case, we want it to be the number 10, to indicate that the student was awarded the 10 points. Note: Only use quotation marks if you want the result to be text instead of a number.
    • Value_if_False: If the value is false — and the student does not live in Gryffindor — we want the cell to show “0,” for 0 points.
  • Formula in below example: =IF(D2=”Gryffindor”,”10″,”0″)

LEARN MORE EXCEL SHORTCUTS

 

 

Leave a Reply