Tip of the Week: Essential Excel Formulas to Know

a laptop computer sitting on top of a table

Microsoft Excel offers essential functions to help businesses excel. Unknown to many, this highly capable piece of software offers countless capabilities that can be extremely useful to companies. Continue reading to learn more about various formulas that can be utilized to help your business grow.

How to Use Excel Formulas

As a cell is selected, a function can be typed into the actual cell itself or into the formula bar found at the top of the sheet. Please note that every formula will start with an equals sign. Following that, coordinates of the cells can be selected or typed to create the formula required. When used correctly, these functions can effectively create spreadsheets that can be updated automatically as inputted data changes. 

Below are a few of the more commonly used formulas that may be useful to you and your business operations.

=SUM

This formula gives the total value of a selected range of cells, allowing ease to add up long lists of values. For example, if there was a list of numbers inserted in Column C, beginning at Row 3 and ending at Row 35, one can type =SUM(C3:C35) to calculate the total, which would then be displayed in the cell that the formula was assigned to.

=AVERAGE

This function will result in the average of the cells that are listed in the formula. For instance, =AVERAGE(A1,B2,C3) will provide the average of values found in cells A1, B2, and C3.

=CEILING and =FLOOR

These functions will round numbers up (ceiling) or down (floor) to the nearest multiple of significance that is inputted. For instance, if one inserted 51.06 in cell D8, utilizing =CEILING(D8,5) would round up to 55 in the cell the formula was applied to. Similarly, =FLOOR(D8,5) would round down to 50.

=CONCATENATE

A feature of Excel that is unknown to many is the use of text-based data. The =CONCATENATE function allows the text in selected cells to be inputted together. For example, if A1 read Comprehensive data backup and B1 read is a necessity for a modern business, the function =CONCATENATE(A1, “ “, B1) would produce a cell that reads Comprehensive data backup is a necessity for a modern business. The same result will be accomplished if =CONCATENATE(A1&” “&B1) is utilized.

=NOW()

By entering this formula into a cell, the system’s current date and time will be inputted, allowing one to keep track of when data was entered.

=TODAY(), et alia.

There are additional functions that may be entered using the system’s current information:

  • =TODAY() provides the current date
  • =DAY(TODAY()) provides the current day of the month
  • =MONTH(TODAY()) provides the month, represented as a number between 1 and 12 

=TIME, et alia.

There are also various functions that allow one to track the time:

  • =TIME() allows the time to be entered, formatted as a serial number in the hours, minutes, and seconds, that will be converted into a typical time format. For example, =TIME(18,28,45) would read as 6:28 PM.
  • =HOUR(NOW()) produces the current hour as a value between 0 and 23 (12 AM to 11 PM).
  • =MINUTE(NOW()) produces the current minute as a value between 0 to 59.
  • =SECOND(NOW()) produces the current second as a value between 0 to 59.

=VLOOKUP() and =HLOOKUP()

These formulas, short for vertical lookup and horizontal lookup, allow one to identify specific values in one column or row within the sheet. For example, a table can be created outlining the top selling products or services, the gross sales of each, the total cost that it can be, the employee with the highest sales, and the least selling employee. By utilizing =VLOOKUP() or =HLOOKUP(), you can select the range of cells to identify the relationship between the data points.

=IF

This function can be utilized to determine if a condition is true or false as it relates to the data. If one wants to determine if the operating threshold has been reached, =IF(D6>5,000,”Yes”,”No”) with your current revenue in D6 can be inserted, allowing an easier way of identifying information at a glance. 

=COUNTIF and =SUMIF

There may be times where a tally or sum is needed. =COUNTIF and =SUMIF make it a lot easier to accomplish this task.

  • =COUNTIF() allows one to tally cells that match the conditions set by selecting the range to be examined and providing the condition that needs to be met. If one needed to tally up the number of customers in certain cities, =COUNTIF(A1:A100, “Smallville”) can be inputted to get the answer.
  • =SUMIF() adds the cells up. Continuing with the previous example, one could enter =SUMIF(A1:A:100,”Smallville”, E:E) to get the total.

To learn more about Microsoft and their business solutions, give us a call at 954.474.2204

Contact Us Today and Check Out Our Blog!

Tip of the Week: Organize Your Email with Filters

a person holding out their hand with email icons on it

Email might be one of the biggest methods of communication in the business world, but it’s hard to stay on top of, even for the best of us. One of the ways you can take back control of your email is to utilize filters. Let’s discuss how you can set these up for your Gmail or Outlook accounts. 

Your Inbox Can Be a Useful Space Again 

Both Microsoft and Google give users the ability to keep their inboxes organized. It doesn’t take a ton of technical knowledge, either. All it takes is a couple of filters integrated right into the software to make it happen. 

Gmail 

Gmail can automatically sort incoming messages into labels using filters. Labels are defined by a specific variable, like maybe you want to set up a label for your pay stubs or to keep reservations/work-related travel separate from the rest of the noise you receive on a daily basis. Labels are great to ensure that you keep things organized, and to make matters better–if messages fit into more than one category–they can be in both places at once! 

You can define your labels by navigating to More in the sidebar and selecting Create new label 

You can also automate this process by going to Settings > Filters and Blocked Addresses > Create a new filter. 

(There is another way to do this, but this option is the most straightforward, so we’ll stick to this one.) 

You’ll see a form appear with variables you can select; these variables will help Gmail identify when an email should be sorted into the label. There are tons of options, like referencing the sender, the subject line, and various keywords. These filters can then send emails to specific labels, where they are easier to read and find later on. 

Microsoft 

Outlook provides users with a similar process, but they are called rules and folders instead. You can set up folders while you are making your different rules, but it’s better to set them up ahead of time. To set them up, access the Mail pane and right-click where you want your new folder to be. When the menu appears, select New folder… and give it a name. 

 

To create your rule, right-click on a message and select Rules > Create Rule. You can select all of the conditions that should apply to this rule, as well as what happens when you receive a message for which the rule applies. You can even make these rules retroactive by selecting the option to Run this rule now on messages already in the current folder. 

 

With these labels, rules, filters, or whatever they are called in your chosen solution, you should find yourself more organized than ever before. What other tips would you like to see us cover in the future? Let us know in the comments! 

Contact Us Today and Check Out Our Blog!

Outlook Keyboard Shortcuts Everyone Should Know

a woman sitting at a table using a laptop computer

Just like the other Microsoft products in the Office suite, like Word and Excel, Outlook has several keyboard shortcuts that you can use to be more productive. We’ve put together a handy guide of some of the most helpful keyboard shortcuts you should keep in mind to get the most out of Outlook.

As always, with quick guides like these, we urge you to consider printing out this message so that you have it as a quick reference. You can use it to up your Outlook game!

Basic Outlook Functions

First, let’s talk about some of the most basic functions of Microsoft Outlook.

For PC

  • Create a message: Ctrl + Shift + M
  • Send a message: Alt + S
  • Insert a file: Alt + N, A, F
  • Delete an item: Delete
  • Reply to a message: Alt + H, R, P
  • Reply All to a message: Alt + H, R, A
  • Forward a message: Alt + H, F, W

For Mac

  • Create a message: ⌘ + N
  • Send a message: ⌘ + RETURN
  • Add an attachment: ⌘ + E
  • Delete an item: Delete
  • Reply to a message: ⌘ + R
  • Reply All to a message: SHIFT + ⌘ + R
  • Forward a message: ⌘ + J

Navigating Outlook

The following keyboard shortcuts are used to move around Outlook and showcase different views.

For PC

  • Switch to Mail: Ctrl + 1
  • Switch to Calendar: Ctrl + 2
  • Switch to Contacts: Ctrl + 3
  • Switch to Tasks: Ctrl + 4
  • Switch to Notes: Ctrl + 5
  • Switch to Folders: Ctrl + 6
  • Switch to Shortcuts: Ctrl + 7
  • Switch to the next open message: Ctrl + .
  • Switch to last open message: Ctrl + ,

For Mac

  • Switch to Mail: ⌘ + 1
  • Switch to Calendar: ⌘ + 2
  • Switch to Contacts: ⌘ + 3
  • Switch to Tasks: ⌘ + 4
  • Switch to Notes: ⌘ + 5
  • Display the next message: CONTROL + ]
  • Display the previous message: CONTROL + [

Creating Items or Files

Sometimes you might need to make items within Outlook. Here are some of the most common types you might need.

For PC

  • Create an appointment: Ctrl + Shift + A
  • Create a contact: Ctrl + Shift + C
  • Create a contact group: Ctrl + Shift + L
  • Create a folder: Ctrl + Shift + E
  • Create a meeting request: Ctrl + Shift + Q

For Mac

  • Create an appointment: (While in Calendar view) ⌘ + N
  • Create a contact: (While in Contacts view) ⌘ + N
  • Create a folder: SHIFT + ⌘ + N

There are so many keyboard shortcuts for Microsoft Outlook that we cannot list them all here, so for even more tips, be sure to check out Microsoft’s official website at this link.

What are some other tips that you would like to see us cover in the future? Let us know in the comments, and be sure to check out our weekly blogs so you don’t miss a beat.