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!

How Office 365 Helps You Get Work Done

How Office 365 Helps You Get Work Done

Microsoft Office 365 takes the familiar Microsoft Office solutions and adds an extra level of usefulness to them in the form of accessibility and collaboration. This week, our tip covers how to use these features of Office 365, in the context of Microsoft Word.

Anywhere Access

Life isn’t perfect, and so you may find yourself needing to work from multiple places. Unfortunately, a desktop workstation isn’t well-suited to travel. Fortunately, if you need to work on aWord document, you can utilize another facet of Office 365 to pull it up: OneDrive. All you have to do is access your Microsoft Office account on another machine, and you will be able to pull up your document from your recent history.

Putting In Your Two Cents

A big part of collaboration is brainstorming and providing feedback. Thanks to the collaborative capabilities of Office 365, not only can you and your team work together on documents, you can add comments to share an insight or annotate a section that needs more work.

Furthermore,your collaborators can add their comments to yours, making it easier to brainstorm better things to include or how to word a certain phrase in your Word document.

Inviting Collaborators

Speaking of your collaborators, Word 2016 makes it easy to add them to your document. Again, using OneDrive, you can share access with anyone else with access to that OneDrive location through Microsoft People. You also have the option to share your document via email in a variety of ways, either sharing editing access or not.

To access this area from within Word, click on the File tab at the top and go to Share.

Clearly, Microsoft Office 365 was meant for teamwork. Have you ever benefited from these capabilities? Let us know in the comments!

Contact Us Today and Check Out Our Blog!

Tip of the Week: 3 Microsoft Office Programs Made for Collaboration

Tip of the Week: 3 Microsoft Office Programs Made for Collaboration

Microsoft is no stranger to improving collaboration with their solutions, as many of them were meant to do just that- improve collaboration.

Below, we’ll discuss three such solutions, and how you can optimize your use of them for collaboration purposes.

OneDrive for Business

While OneDrive for Business’ collaboration really shines when paired with Microsoft Teams, a user isn’t constrained to working within Teams exclusively. OneDrive also enables file sharing with someone external to the team in Word, Excel, or PowerPoint. In the application, click Share, which can be found in the upper right-hand corner. You will be presented with a dialog box that gives you a few options. Either you can enter the person’s name or email to send it, or you can get a copy of the link to share with them through other means.

However, if you’re trying to share the document with someone outside of your organization,you will need to select the drop down at the top of the dialog box and say that Specific People can access your file.Once this has been done, these approved users can all edit the file at the same time, in real time, assuming that the link was sent with Allow Editing checked in the dialog box. Keep in mind, this is only for documents that are saved in OneDrive.

Outlook Email

I can already hear you: “Well, of course an email client will help with collaboration!” This is true, however, it is also true that Microsoft has augmented Outlook 2016 with Office 365’s Groups feature. As a result, Outlook has additional collaborative features, most notably, a shared space for a team to leverage for their collaboration. Essentially, Outlook Groups have access to a shared calendar, team inbox, and a document library. These tools allow groups to hold their own conversations, schedule meetings more efficiently, and generally communicate more effectively. Groups are also relatively easy to create, assuming that they have been enabled by your organization’s IT resource:

  • In the Navigation Ribbon, clickthe Home tab.
  • In the Groups section, click New Group.
  • Name your new group in the Choose a name field. The group will be given a suggested email address in the field immediately below, if it is available. Remember, your group name cannot be changed.
  • In the Description field, write a brief mission statement for your group, remembering that it will be shared in every welcome email sent to new group members.
  • Set your group’s Classification from the options provided by your organization.
  • Identify your group’s Privacy level. This defaults to private, where group content is only visible to approved group members, while there is a public option, where anyone in your organization can join and view content.
  • You also have the option to send all of the group’s conversations and any events to all members’ inboxes. They can later change this setting.
  • Once you click Create, you’re done!

Skype for Business

Again, yes, this is technically an application designed for communicating and collaborating, but it also integrates with Outlook to communicate a very useful detail: whether or not a person is available at a given time, depending on what a contact’s Outlook calendar dictates. Therefore, if you’re currently in a meeting, your Skype presence will reflect that you are unavailable. Of course, this feature also allows you to schedule a meeting, even allowing you to list out the topics that meeting will cover and the tasks that you plan to accomplish during a conversation.

Using these Microsoft solutions can help you improve collaboration and communication in your business. Tell us, do you use these tools already, or do you have a different set of favorites that allow you and your staff to collaborate?

Let us know what they are in the comments!

Tip of the Week: How to Add a Watermark in Microsoft Word

Tip of the Week: How to Add a Watermark in Microsoft Word

Adding a watermark to a document is a great way to very visibly share a message about the contents of a document. Microsoft Word makes it pretty easy to do it yourself and leverage the associated benefits. Below, we explain how.

Why They Work

Watermarks are effective for a lot of the same reasons that a billboard often is: it’s a concise and clear message, printed in a very in-your-face-way. The big difference is that sometimes, a watermarked document can almost literally be in someone’s face. This works to your advantage.

A watermark is really difficult not to see, so if you need someone to know that a document is confidential, having it display how CONFIDENTIAL it is will likely catch their eye and respect the need for discretion. Oftentimes, legal requirements or security obligations make the addition of a watermark on certain documents a necessity.

In short, a watermark is a quick and easy way to share the nature of the information in a document, whether it is just a DRAFT or if it happens to be an INVOICE that requires immediate attention. Essentially any message you need to convey can be incorporated into a watermark.

Creating a Watermark in Word

Microsoft has made it fairly easy to set a watermark into your documents. If you’re using Word
2016:

  • Open the document that you need to add the watermark to, whether it is completed or still needs to be edited.
  • Access the Design tab and select Watermark.
  • You now have the option to either select from Word’s collection or add a custom piece of text or image to use. If using one of Word’s, simply make your selection from their menu.
  • If adding a custom watermark, instead select Custom Watermark.
  • Select either Picture or Text, and then insert the text or the image file that you want to use. Word allows you to tweak it further from there as well.

Whatever your purpose, whether it’s sharing a message or customizing your official company materials, a watermark does the trick. What other tips would you like us to go over?

Leave your suggestions in the comments!

Contact Us Today and Check Out Our Blog!