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: Use Dark Mode to Go Easy on your Eyes

a black and yellow resume with icons on it

Staring at a bright computer screen all day can bother your eyes, especially if it’s done on a routine basis. We know that staying away from computers and devices is something that we cannot get away from in the business environment, but you can take steps to make it more manageable and easier on your eyes through Dark Mode.

Here are some ways to enable darkness mode for your Windows operating system and its applications. However, take note that most of your desktop applications will not be affected by default––only the ones associated with Microsoft and Windows. You should always check the settings for other applications, as they may have darkness mode built into them.

Enable Dark Mode for Windows

The blanket setting for dark mode in the Windows operating system can be found here: Settings > Personalization > Colors. You should see the option for dark mode under Choose Your App Mode. This does not change all of the settings for all of your applications, so be sure to check the settings for any apps you use regularly.

Enable Dark Mode for Edge

Microsoft Edge is Windows’ default web browser, so (as expected) it has a dark mode setting. You can enable it through the three-dot menu on the right side of the screen. Select Settings, navigate the drop-down menu and select Choose A Theme. From here, choose Dark.

Enable Dark Mode for Microsoft Office

Microsoft Office has a dark option that can be enabled through the settings. You can do so by opening any Office application, like Word or PowerPoint, then going to File > Options. Under the General tab, click on Personalize Your Copy of Microsoft Office. You can then set your Office theme to Black.

That’s all you need to do; dark mode should be enabled on your Microsoft products. Other applications might have extensions or add-ons that will allow it, so make sure you do a quick search to see how you can make dark mode happen for your specific applications. Just make sure they are legitimate––you don’t want to put your infrastructure at risk trying to make things more convenient for yourself.

We hope you have found this blog helpful! If so, why not share it with some of your friends or colleagues?

What are some other tips 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.

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.