Category: Office 365

Excel's STOCKHISTORY Function is Now Available

3/4/2021

Recently added to Excel is the new STOCKHISTORY function. You can use this function to retrieve historical prices for stocks as of a given date or range of dates. Read on, and you will learn how to use this feature to query historical securities prices into an Excel workbook.

Introducing STOCKHISTORY

Excel’s STOCKHISTORY function is finally rolling out to subscription-based versions of Excel. Unfortunately, this function is not yet available with perpetual licenses of Excel, such as Excel 2016 and Excel 2019. Presumptively, Microsoft will add the function to the next new version of Excel.

With this feature, you can retrieve historical stock prices for stocks by simply entering a few variables into a formula. Moreover, you can retrieve values for a single date or a range of dates. Further, if you choose a range of dates, you can designate daily, weekly, or monthly intervals. STOCKHISTORY displays date and closing price by default. However, you can optionally choose to show opening price, high price, low price, and volume if desired.

Using STOCKHISTORY

The syntax for using the STOCKHISTORY function is relatively simple, as indicated below. However, note that of the arguments available, only the stock and start_date are required. Thus, a formula using STOCKHISTORY could be as simple as =STOCKHISTORY(“MSFT”,“1/29/2021”). Of course, this formula returns the closing price for a share of Microsoft stock on January 29, 2021.

Additionally, you can create more sophisticated formulas using STOCKHISTORY if your needs require additional information. Specifically, the full syntax of a formula can include all the following items.

STOCKHISTORY(stock, start_date, [end_date],[interval],[headers], [property0], [property1] [property2], [property3], [property4], [property5])

stock: The identifier for the financial instrument targeted. This reference can be a ticker symbol or a Stocks data type.
start_date: The earliest date for which you want information
end_date (optional): The latest date for which you want information
interval (optional): Daily (0), Weekly (1), or Monthly (2) interval options for data
headers (optional): Specifies if the formula returns additional header rows with the array
property0 – property5 (optional): Specifies which information to include in the result, Date (0), Close (1), Open (2), High (3), Low (4), Volume (5).

Extending the previous example, we can create more powerful formulas that use the new function. For instance, we can use the following formula to use the STOCKHISTORY function to generate a listing of closing prices for a range of dates:

=STOCKHISTORY(“MSFT”,“1/1/2020”,“12/31/2020”)

To illustrate, Figure 1 below provides an abbreviated set of results from the formula shown above.

Figure 1 - Sample Results Using STOCKHISTORY

Another Example of STOCKHISTORY

Next, the ensuing example of the STOCKHISTORY function incorporates optional arguments to add columns for the Open price, High price, Low price, and Volume for each trading interval.

=STOCKHISTORY(“MSFT”, “1/1/2020”, “12/31/2020”,,1,0,1,2,3,4,5)

For example, Figure 2 illustrates an abbreviated set of results using this formula.

Figure 2 - STOCKHISTORY with Optional Arguments

Of course, you can use STOCKHISTORY results in the same fashion as if you entered the data manually.

Summary

In short, STOCKHISTORY is one of the most widely-anticipated functions added to Excel in recent years. If you have a subscription-based version of Excel, you should already have access to this feature or receive access soon. Importantly, you can use STOCKHISTORY to retrieve stocks’ historical prices and incorporate them into other calculations in your spreadsheets. Therefore, the next time you need to perform research to obtain historical data about a stock, consider using STOCKHISTORY. Most importantly, if you do, you will reduce the amount of time you spend retrieving data.

You can learn more about STOCKHISTORY and other Excel features and functions by participating in a K2E Canada Inc. training class. Additionally, you can learn more about STOCKHISTORY by reading this Microsoft article.

View a Video Tip That Demonstrates STOCKHISTORY

Tommy Stephens

Excel Dynamic Arrays

1/10/2021

A Dynamic Array formula is any formula that has the potential to return multiple results. There are several new functions in Excel for Office 365 that have this ability, including Sort, Filter, and Unique. In this post we will have a look at Sort and Filter.

The first thing to keep in mind is that the results from these Dynamic Array formulas will “spill” into adjacent cells; therefore, we need to plan for the possible spill range by allowing ample space in adjacent cells. This spilling is by design; and, in the event the spill range is not large enough for the results, Excel returns a #SPILL! error.

The second item to consider is that references used in Dynamic Arrays between workbooks need both workbooks to be open; Excel returns a #REF! if they are not.

Spill

To understand better what spill means and how dynamic arrays work, let’s start with the data below and create a formula to summarize the information.

The data is in a table named Payments. In cell G5, enter the formula =payments and press Enter. The data is now in G5:J22 using this single formula. While in the data range, you will notice an outline around the data indicating that it is part of a dynamic array. The formula is only in cell G5 and, therefore, all other cells in the array will show the formula as a light gray in the formula bar as shown below.

As shown below, in the event of other data residing in the range used by the dynamic array formula – for example, cells M12:O12 – completing the formula will result in the #SPILL! error. Keeping cell L5 highlighted, Excel will show where the data will spill, providing clear guidance on which data is causing the error. To resolve the problem, either move the dynamic array formula cell or clear the data in the spill area. Additionally, as shown below, Excel provides an error-checking tool and a quick way to Select Obstructing Cells if you want to move them outside the spill range. Once you clear the obstructing cells from the spill range, the dynamic array formula should complete, unless one of the following four issues arises.

The spill range includes merged cells;
The dynamic array formula is inside of a table;
The system is out of memory; and
The sequence is volatile, such as with a RANDBETWEEN sequence.

Spill Range Reference

With only the one cell in the spill range containing the formula, the question becomes how to refer to all of the data in the range. The answer is to use the spill range operator which is the # symbol. In the previous example, to refer to the entire spill range, the reference would be =G5#. This does have a limitation in that it is not supported when linking to an external workbook.

Sort

The process to sort by formula before Dynamic Arrays was very complicated and, therefore, seldom used. With the Dynamic Array function SORT, the process is simply to use the formula below.

=SORT(array,[sort_index], [sort_order],[by_col

Using similar sample data, as in the previous example, in cell G2, input the formula =SORT(sortdata,3,1). The data is in a table named sortdata; the number 3 is the column to use as the sort, and the number 1 is for ascending order.

If you want to sort by more than one index, you can use an array constant. For example, to sort the data by Account ascending and Amount descending, the formula would be =SORT(SortData,{3;4},{1;-1}). The results of these formula-based sorts are shown below.

SortBy

The SORTBY function allows you to sort the contents of an array based on the values in a corresponding array. The syntax for the function is =SORTBY(array, by_array1, sort_order1,….). For example, to do the same sort as we did with the array constant, the formula would be as follows.

=SORTBY(SortData,SortData[Account],1,SortData[Amount],-1)

Filter

The next dynamic array function is FILTER, which allows filtering of data into a new array. The syntax for the function is =FILTER(array, include, [if_empty]). To have more than one condition, use the plus sign between them for either and the multiply sign between them for both.

As an example, in Figure 18, we want to retrieve all rows from the table named FilterData that have the item stamps. The formula would be as shown below.

=FILTER(FilterData,FilterData[Item]=I1,"Item not found")

To retrieve the information related to stamps in the sales department the formula is as follows.

=FILTER(FilterData,(FilterData[Item]=I1)*(FilterData[Department]=I2),""

Dynamic Arrays at the time of creating this post is only available in the Office 365 Monthly Channel (Targeted)

dynamic_arrays.xlsx
File Size:	33 kb
File Type:	xlsx

Download File

Ward Blatch

Technology Focused Learning - Why It's Now a Business Necessity

12/2/2020

Planning your professional education curriculum is a balancing act, with many competing options and mandates, including technology focused learning. In the past, many have viewed technology focused learning somewhat dismissively. The following sentiment summarizes the attitude many have on this topic.

Why do I need to learn more about technology? I already use Excel and email and I can open PDF documents and navigate them with ease. What more is there to learn?

Well, the short answer is there is plenty more to learn, and it’s changing by the day. And that is why technology focused learning is a business necessity today to remain competitive.

The Ever-Changing Technology Landscape and It's Impact on Learning Plans

Consider the following four examples of how technology has changed how we work over just the past few years.

CLOUD COMPUTING

Less than a decade ago, most organizations were still running most of their applications from local servers and resources. In today’s world, having a fundamental knowledge of Cloud Computing is mandatory for success. Why? Because knowledge workers are now running five Cloud-based applications, on average. Given this, it only seems fitting that Cloud Computing should be a component of our technology focused learning efforts.

INFORMATION SECURITY

Information security was once thought to be the sole responsibility of the IT staff. Now, given the explosion of ransomware, phishing attacks, social engineering, and countless other schemes, security is a shared responsibility. No doubt, our IT staffs must continue their efforts, and likely enhance them to ensure adequate protection. But end users must become educated on the risks and be aware when something seems to be out of order. In other words, information security is a now a shared responsibility. In this environment, our information will be only as secure as the least secure link in the chain. Against that backdrop, can we afford not to include security training in our professional development curriculums?

SUBSCRIPTION-BASED SOFTWARE

Subscription-based software – such as Office 365 and Acrobat DC – is further complicating matters and escalating technology focused learning needs. For example, if you are an Office 365 subscriber, Microsoft is adding new features to your applications periodically. This means that you are getting new tools added to Excel, Word, PowerPoint, and other Office applications. Yet, these new features do not increase your productivity unless you are a) aware of their presence and b) knowledgeable on how to use them. Accordingly, on-going learning is necessary to ensure the you are maximizing your return on investment from your subscription-based applications.

EMERGING TECHNOLOGIES

Emerging technologies are coming to market rapidly and some organizations are implementing them almost as soon as they appear. A fitting example of this is Robotic Process Automation (RPA) – a technology that you may already be hearing much about. In a nutshell, RPA allows you to use software-based robots – “bots” – to automate tasks that are rote and repetitive. Stated differently – when implemented properly – this technology can free information workers from manually performing these same tasks. In doing so, it allows these team members to focus on tasks that have greater value to the organization. Further, RPA can reduce labor costs and increase overall accuracy and efficiency.
Of course, RPA is but one of many examples of emerging technologies that are on the precipice of changing how we work. Unfortunately, those who remain unaware of RPA and similar emerging technologies will not experience the benefits as soon as they could or perhaps should. Again, this only reinforces the need for technology education.

Planning Your Technology-Focused Training

FOCUS ON MISSION-CRITICAL CONTENT FIRST

The first step in planning your technology-focused learning curriculum should likely be to consider the content you need to focus on. For example, if your organization will be implementing any new hardware, software, or other services during the year, then you and your team will likely require training in that area. The extent of that training will depend upon the complexity of the technology you are implementing relative to the current skills and knowledge of you and your team members.

ONGOING MAINTENANCE

A possible second step in planning your curriculum is to consider the need for “ongoing maintenance” training. Included in this category are updates on specific applications and services you already use. For example, if you are an Office 365 subscriber, participating in an update session of relevant new features should be near the top of your list. Likewise, assuming you are running Windows 10, gaining an understanding of new features and tools embedded in your operating system should be a priority. Remember if you don’t know about these new features – and therefore can’t use them – you will not be able to maximize ROI.

SECURITY TRAINING SHOULD BE MANDATORY

As mentioned previously, security training is a virtual necessity in today’s increasingly risky world. Typically, I recommend four to eight hours of security training each year for most business professionals and their team members. You can easily accomplish this during staff meetings or even “lunch-and-learn” type environments; these methods are particularly useful when trying to raise all team members’ awareness of the risky world in which we operate and continually reinforce their responsibilities.

SKILLS ENHANCEMENT TRAINING

In addition to the three categories outlined above, you should set aside time to learn relevant new skills that can make you more productive. To illustrate, consider some of the ubiquitous opportunities available in Excel. Recent enhancements to Excel in the areas of Power Query, Data Models, and Power Pivot set the stage for completely overhauling how many accounting and financial professionals create reports. The same can be said of Microsoft’s Power BI tools too. Unfortunately, relatively few accounting and financial professionals currently have the knowledge to take advantage of these tools. This means that most remain locked-in to stale, inefficient, and outdated reporting processes. When planning your curriculum, be sure to invest ample time in new topics that can expand and improve your skill set.

BECOME FAMILIAR WITH EMERGING TECHNOLOGIES

As indicated previously, emerging technologies appear at an unprecedented pace. Allocate time in your training schedule to become familiar enough with these technologies to know whether they could potentially become useful in your organization. This is not to say that you need to become an expert on each innovative technology. Rather, you should become aware enough of each of these tools to know whether they will impact your business. If you reach the conclusion that they are relevant, then you should likely invest more time in the future to gain a deeper understanding of the technology and its related benefits and risks.

Where to Obtain Your Technology-Focused Learning

TRADITIONAL LEARNING OPTIONS

The number of options you have available today for accessing technology focused learning is virtually unlimited. Formal training programs are offered by many companies (including mine, K2 Enterprises) and can often be scheduled through state CPA society organizations. Options available in this distribution channel include in-person seminars and conferences, webcasts, and on-demand sessions.

Another option is to engage one of the content providers discussed in the preceding paragraph to bring the training directly into your organization. The major benefits associated with this approach include: 1) the ability to customize the content to meet specific needs, 2) potentially reduced costs, and 3) the convenience of scheduling the training at a time and place that is convenient for participants.

Additionally, major technology vendors often sponsor their own conferences, which usually include substantial learning options. These options can be particularly useful if you have a specific training need that centers around a technology sold and supported by the sponsor of the conference.

SOME ALTERNATIVE LEARNING OPTIONS

You may be able to leverage in-house expertise to create and deliver content to other team members. However, you should be aware that this can prove to be an expensive proposition if the discussion leader will need to develop appropriate materials for participants. Also, you should check with the appropriate body to determine if professional education/development credits can be claimed in these environments.

Finally, don’t forget all the on-line resources that are available to you today, including something as simple as YouTube. You may be pleasantly surprised to see how much useful content is available on YouTube and similar platforms.

Summary

Technological innovation continues at unprecedented rates. And, consequently, so too does the need for technology focused learning. Unfortunately, in far too many cases, business professionals overlook the need to improve their technology skills. To address this issue, we recommend technology focused training that encompasses five areas: 1) Mission-critical content, 2) On-going maintenance, 3) Security training, 4) Skills enhancement training, and 5) Emerging technology familiarity. By allocating sufficient time to learning activities in these areas, you will be on your way to maintaining your skills. You will also gain new ones that will improve your productivity and efficiency now, and in the future.

Click Here to learn more about technology-focused training options from K2E Canada Inc.

Tommy Stephens

Artificial Intelligence Meets Microsoft Office

11/7/2020

Many have written about the future of Artificial Intelligence (AI) and its potential in our personal and professional lives. So far, most of the literature describes the impacts as “futuristic” and arriving several years from now. But did you know that you can take advantage of AI today? More specifically, AI is available in many of your favorite Microsoft Office applications, and taking advantage of these tools can provide tremendous benefits. Read on, and in this article, you will learn about four specific instances of artificial intelligence in Office applications.

Word's Editor Feature

Let me begin by admitting that I do not consider myself a good “native” writer. That means that as I write articles such as this, I typically struggle with sentence structure, punctuation, and the like. In the past, I engaged an editor to assist me in “cleaning up” my text. However, with Word’s Editor feature – which became available with Word 2016 – the need for outside assistance is diminished dramatically.

Figure 1 - Word's Editor Feature As An AI Tool

As shown in Figure 1, Editor examines my document it real-time and alerts me to issues that potentially require action. In the example presented, Editor recommends that I address two items – Grammar and Vocabulary. I can click on issues that Editor raises, and it provides recommendations to improve the readability and quality of my document.

For example, clicking on Vocabulary opens the Editor pane shown in Figure 2. In that pane, I can choose to accept or reject the AI-driven suggestions. I can also modify the setting Editor uses to deliver its Artificial Intelligence-based recommendations to me. In sum, I find Editor to be a terrific assistant that helps me create documents that are free of punctuation and grammatical errors. Moreover, Editor’s recommendations help create passages of text that are easier for readers to comprehend.

Figure 2 - Editor's Vocabulary Pane

Grammarly - Another AI Tool for Word

In addition to Editor, I use Grammarly (www.grammarly.com), a tool that provides a second set of Artificial Intelligence-based recommendations on the documents that I create. Grammarly is a subscription-based service, with a free option available. However, I found the Premium subscription ($139.95 annually) to suit my needs better.

Grammarly’s Premium subscription appears to have a more comprehensive set of rules and writing standards than Editor. Further, Grammarly allows me to define the tone I want to convey in my text, and, upon doing so, it tweaks its recommendations to ensure I meet my objectives. It even works in Outlook, helping to ensure that my outbound email messages are error-free.

Both Editor and Grammarly provide outstanding (and often needed!) functionality. The Artificial Intelligence engines that power these tools are terrific. If you are currently using Word 2016 or newer, check out Editor. Additionally, if you want to go deeper with AI as a means of improving your writing, take a careful look at a Grammarly subscription.

PowerPoint's Design Ideas Feature

Artificial intelligence meets Microsoft Office in other applications besides Word. For example, PowerPoint now boasts a feature known as Design Ideas, which you will find on the Home tab of the Ribbon. Design Ideas appeared with PowerPoint 2019. This feature provides users with recommendations to improve the appearance of a slide in a presentation.

To illustrate the effectiveness of the AI-based Design Ideas feature, consider the image in Figure 3. In that image, I used PowerPoint’s Design Ideas feature to convert the tired-and-stodgy bullet point slide at the top of the image to the more modern and contemporary one at the bottom of the image. And all that was necessary to achieve those results was to click Design Ideas on the Home tab of the Ribbon. Upon clicking Design Ideas, PowerPoint provided ten alternatives to the original slide.

Figure 3 - Using Design Ideas In PowerPoint

Like Editor and Grammarly, Design Ideas in PowerPoint uses Artificial Intelligence to help us get better results in less time. More specifically, Office Intelligent Services powers Design Ideas and other AI-based features available in Office.

Artificial Intelligence in Excel

Not to be outdone, Artificial Intelligence meets Microsoft Office in Excel too. Features such as Stock and Geography Data Types, Dynamic Arrays, and the ability to insert data into a spreadsheet by taking a picture are all examples of Artificial Intelligence available in our spreadsheets. So, too, is Excel’s Ideas feature.

Ideas is an Excel tool that can quickly analyze your data and help you uncover trends, outliers, and other observations. Importantly, Ideas can help you identify issues that you may have missed with the human eye. When you use Ideas, you are taking advantage of Artificial Intelligence to analyze data more thoroughly and faster than could be done with traditional manual procedures.

To take advantage of Ideas, click in the data in your spreadsheet. Then click Ideas on the Home tab of the Ribbon. Excel will take over from there and create numerous charts, PivotTables, and other analyses for you, resembling the output shown in Figure 4. Moreover, you can even type a command such as “Total Order Amount” directly into the Ideas pane, and Ideas will respond with an answer. For those who are afraid that they might miss key trends, observations, and outliers in their data, the Artificial Intelligence features available in Ideas may be the solution needed.

Figure 4 - Using Excel's Ideas Feature

Summary

For many, Artificial Intelligence once seemed like something that was a great concept but would never materialize at a practical level. Yet today, Artificial Intelligence appears quietly in ways that many have yet to recognize. Examples of this spread include the ways that Artificial Intelligence meets Microsoft Office and enhances the various Office applications. If you have not yet explored these tools, now might be the time to see how helpful they can be for you. Further, keep your eyes open for other new features in applications and services that you use every day – many of them will likely incorporate Artificial Intelligence too!

Interested in learning more about Artificial Intelligence or Microsoft Office Applications? If so, consider a K2 Enterprises CPE course. Click here to view your options.

Tommy Stephens

The Best Ways to Collaborate with Office Documents

11/7/2020

Collaboration is more important today than it ever has been. With many team members working remotely, it can be challenging, to say the least, to share documents so that all interested parties can access, edit, and comment on them. Fortunately, the evolution of Microsoft Office includes features that can streamline, simplify, and enhance collaboration. Read on, and in this article, you will learn the best ways to collaborate with Office documents.

The Need to Collaborate

Before addressing the issue of “how,” let us explore “why.” Stated differently, why is collaboration so important? And why are yesterday’s methods inadequate in today’s environment?

Even before the pandemic-driven work-from-home (WFH) environment, the need to collaborate was significant. Consider, for example, the ubiquitous Excel workbook that many organizations use for at least a portion of their budgeting processes. Multiple users typically need to interact with that workbook, entering data, and generating reports. Typically, “collaboration” in this case involved emailing the workbook to all interested parties or using Excel’s Shared Workbook feature.

Other everyday collaboration needs include circulating a Word document among team members for editing and “wordsmithing.” Yet, Word does not offer a “Shared Document” feature, similar to Excel’s “Shared Workbook” feature. Therefore, “collaboration” with Word documents almost always meant emailing copies of the file to all interested parties.

Similarly, you might need to involve other team members in creating and editing a PowerPoint presentation. Unfortunately, like Word, PowerPoint does not offer a “Shared Presentation” feature; again, collaboration almost always involved emailing documents.

Drawbacks to Traditional Collaboration Methods

If email is the primary tool for collaboration, at least two adverse outcomes can occur. First, multiple copies of the same file immediately appear. As numerous users edit these copies, they no longer contain the same data. This practice, of course, leads to inconsistency in reports. Alternatively, Excel’s Shared Workbook feature generally mandates storing the workbook on a server that all parties can access. Of course, in a WFH environment, this requirement is not always satisfied easily.

Further, using the Shared Workbook feature imposes limitations on available functionality. Additionally, as pointed out above, neither Word nor PowerPoint offers a “Shared Document” feature. And finally, Microsoft has indicated that they will remove the Shared Workbook feature from Excel in future releases. As demonstrated, legacy versions of Microsoft Office applications present challenges when collaborating with others, both inside and outside our organizations.

Sharing Office Documents

Beginning with the 2010 release of Microsoft Office, improvements to collaboration techniques started to appear. At that time, Microsoft added the ability to share an Office document with internal and external users. This process relies on sending an invitation to the other users. It also requires you to store the file you wish to share in Microsoft’s Cloud-based OneDrive, OneDrive for Business, or SharePoint Online.

During the ensuing ten years, this process has improved significantly. It now provides detailed options for controlling who can access the document and what they can do with it once accessed. As shown in Figure 1, you can initiate the process of sharing your Cloud-stored documents by clicking Share near the upper right corner of the document’s window. You can read more about this process by clicking here to access an article on this feature published by Microsoft.

Figure 1 - Sharing An Excel Document With Other Users

Adding Comments and Notes to Documents

Another relatively new enhancement that facilitates collaboration is the ability to add Comments to Word and PowerPoint documents. Regarding Excel documents, you can add Comments and Notes.

Comments facilitate adding feedback to a document, such as suggestions to make it more usable to the intended audience. For Word, PowerPoint, and Excel, you can add Comments by clicking the Comment button near the window’s upper right corner. You can also add Comments using a right-click of your mouse. Once you add a Comment, other users can reply to it, facilitating a “threaded conversation” within the document, as shown in Figure 2.

Figure 2 - Examples Of "Threaded" Comments In Word

As mentioned above, you can also add Notes to an Excel document. A Note is a means of documenting the contents of a cell. Typical uses of Notes include references to source documents, citations, and other forms of documentation that explain where the cell’s data originated. You can add a Note by right-clicking on a cell and choosing New Note. You can also add a Note by selecting Notes from the Review tab of Excel’s Ribbon.

Using Teams to Collaborate

At its core, Microsoft Teams is a communication and collaboration tool. Therefore, as you might expect, collaborating on documents occurs with ease when using Teams. Should you choose to save an Excel, Word, or PowerPoint document in Teams, multiple people can access and edit the file simultaneously, as shown in Figure 3. Of course, this functionality requires team members to possess appropriate security rights for the Team and Channel. If that is the case, no additional actions are necessary to collaborate on a document, making this perhaps the easiest way to facilitate collaboration in organizations of all sizes.

Figure 3 - Collaborating On A Document In Teams

Summary

The need for collaboration tools and techniques is not a new one. But the need for improved collaboration options on Microsoft Office documents is magnified in WFH and decentralized environments. Fortunately, Office now offers some outstanding features for sharing and collaborating with others, both inside and outside our organizations. So, instead of legacy tools and workarounds, take advantage of features such as sharing files, adding notes and comments to documents, and Teams. These are the best ways to collaborate with Office documents!

Significant Changes for Microsoft Office Are On The Way

9/7/2020

Significant changes for Microsoft Office – a “staple” technology in most businesses – are on the way! Although Microsoft announced these changes previously, many professionals remain unaware of them. Further, many have not yet prepared to manage through these changes to minimize disruption. In this article, you will learn about the coming changes for Office. Moreover, you will also learn what you should do now to prepare for them.

Technical Support is Ending Soon for Some Versions of Microsoft Office

Microsoft announced in December 2019 that Office 2016 for Mac and Office 2010 reach their end of support on October 13, 2020. After that date, Microsoft will no longer provide technical support, bug fixes, or security updates for either of these products. The applications will continue to operate as they do presently. However, over time you might experience performance, reliability, or security issues with these applications.

The obvious action item related to this issue is to make plans now to upgrade to a newer version of Office, including one provided through an Office 365 or Microsoft 365 subscription. Under the subscription models, you can avoid substantial cash outlays to upgrade licenses throughout your organization. Additionally, subscription models help you to avoid the risk of obsolescence and lack of technical support. In addition to considering an Office 365 or Microsoft 365 subscription, you may also want to take a look at Zoho One and G Suite. Both these offerings compete with the Office franchise available from Microsoft. Further, they offer some unique characteristics and options that might make them a more attractive option in some cases.

Only Supported Versions of Microsoft Office Will be Supported When Connecting to Office 365 Services

Many professionals use Microsoft Office applications such as Outlook, Excel, and Word to connect to various Office 365 services. Examples of these services include OneDrive for Business, Exchange Online, and SharePoint Online. In some of these cases, traditional, perpetual licenses of Office applications connect to Office 365 services. After October 13, you will need to be running Office provided through an Office 365/Microsoft 365 subscription, Office 2019, or Office 2016 to connect to Office 365 services. If you are running older versions of Office applications and connect to Office 365 services, no technical support will be available from Microsoft.

Does This Change Mean You Won't be Able to Access Your Email Using Older Versions of Outlook?

No, but there will be no technical support available if you are trying to connect older versions of Office applications – including Outlook – to Office 365 services, such as Exchange Online. More broadly, Microsoft stated in a recent email message to some customers:

If your organization is running any Office client older than Office 2016, Microsoft will not take any active measures to block them from connecting to Office 365 services. However, legacy clients attempting to connect to a modern, always-up-to-date cloud service may experience performance and reliability issues. Customers will face an increased security risk, and may find themselves out of compliance depending on specific regional or industry requirements. Microsoft Help may not be able to resolve issues that arise due to unsupported service connections.

To see a complete view of the support lifecycles for various Microsoft Office versions and Office 365 services, click here.

Summary

The world of software licensing and support continues to get more complicated in some respects and, arguably, simpler in others. With numerous versions of Office still in use today across multiple operating systems, it can be challenging for software publishers to find the right approach to supporting legacy versions of their applications. Further, end-users need to know that their software is not going to simply stop working because of a hasty announcement from their software publisher. Fortunately, more users moving subscription models should help to ease these conflicts. However, in the meantime, please make sure you pay attention to announcements such as the ones discussed above to ensure you don’t encounter any service interruptions.

Tommy Stephens

Excel's New LET Function Can Simplify Your Formulas

9/7/2020

If you access Excel through an Office 365/Microsoft 365 subscription, get ready for the new LET function. LET began appearing in select releases of Excel in July 2020 and will continue to roll-out over the upcoming year. In essence, LET allows you to declare and store a variable inside a formula. Once you establish the variable with LET, you can then use that variable repeatedly in the same formula. In this article, you will learn how Excel’s new LET function can simplify your formulas.

LET Fundamentals

“Old-school” programmers no doubt remember the days of declaring variables in a computer program using a LET function. While writing code, programmers might include a statement such as “LET x = 100.” With “x” established as the value of “100,” they would then use that variable in other calculations. Excel’s new LET function provides essentially the same functionality, with one notable difference. With LET, you can only use the variable you declare in the same formula that contains the LET function.

As a simplified first example of working with LET, consider the following formula in Excel.

=LET(x, 100, SUM(x, 1))

The formula shown first establishes “x” as a variable with a value of “100.” It then adds that value to “1” to produce a calculated value of “101.” The fundamental example provided is just that – a simple example to introduce LET. Let’s turn our attention to some more practical uses of LET. But first, let’s describe why LET is potentially beneficial to Excel users.

LET Benefits

According to Microsoft, LET offers two primary benefits: 1) Improved Performance and 2) Easy Reading and Composition. More specifically:

Improved Performance.If you write the same expression multiple times in a formula, Excel calculated that result numerous times. LET allows you to call the expression by name and for Excel to calculate it once.
Easy Reading and Composition.No more having to remember what a specific range/cell reference referred to, what your calculation was doing, or copy/pasting the same expression. With the ability to declare and name variables, you can give meaningful context to yourself and consumers of your formula.

From the above, we can conclude that LET allows us to improve our formulas by making them easier to understand while simultaneously speeding calculation times.

A Practical Example of How the LET Function Can Simplify Your Formulas

In this illustration, let us assume that a loan officer is deciding whether to approve a mortgage on a house. Further, the bank’s policy is to charge 0.5% more interest if the applicant’s credit score is less than 800. In this case, we can use the following formula to calculate the monthly payment based on the inputs shown in Figure 1.

=LET(Rate,(IF(B4>799,B2,B2+0.005)),PMT(Rate/12,B3*12,-B1))

Figure 1 - Using LET to Calculate a Payment Based on a Credit Score

In the formula pictured above, the LET function declares a variable named “Rate.” Further, the LET function uses a nested IF function to retrieve the value from cell B4 and determine if it is less than 800. If so, the interest rate increases by 0.5%. Finally, the formula calculates the monthly payment by incorporating the “Rate” variable established by the LET function. In this example, using LET facilitates creating a more concise calculation than would otherwise be possible.

Further, the advantages of using LET increase as the complexity of the calculation increases. For example, if the loan officer establishes the interest rate based on three tiers of credit scores instead of two, the value of using LET magnifies significantly.

Summary

Excel’s new LET function allows you to simplify calculations in Excel by declaring variables within a formula. Once you establish such a variable, you can use it repeatedly in the same expression to ease the process of creating complex calculations. In this context, Excel’s new LET function can simplify your formulas. Therefore, as this feature becomes available to you, carefully consider how you can use it to get better results in less time.

Learn More About LET in this Video

Tommy Stephens

Microsoft Begins Rolling Out The New 'Briefing' Feature

7/5/2020

Microsoft has begun rolling out the new Briefing feature to Office 365/Microsoft 365 Administrative users. Briefing is a service that sends a daily email alerting you to upcoming items on your calendar. In this article, you will learn how you can use Briefing to prepare better for your workday.

Who Gets Briefing?

Eventually, Microsoft will make Briefing available to all Office 365/Microsoft 365 users with a subscription that includes Exchange Online services. However, the initial roll-out of Briefing will be for Administrative users only. This will allow them to prepare to answer any questions that ultimately might arise from end-users. Further, if desired, Administrative users can disable the Briefing service for a single user, a group of users, or the entire organization.

How Does This Feature Help Me?

Briefing analyzes information that is recorded on your Outlook calendar to help you prepare for and stay in control of your workday. Within the first two hours of the start of your workday (as you have it established in Outlook), Briefing sends an email to you that alerts you to relevant issues to help you prepare for the workday. Included in the content of that email are the following items.

Any outstanding commitments, requests, and follow-ups that you might own to other team members or they might owe to you.
Links to documents that are related to meetings scheduled for the current day, to facilitate review before the meeting.
Suggested “focus time” that you can schedule to work on items where collaboration is not required.

For each task identified in the Briefing email, you can indicate that it is not something that you need to address, in which case it will not appear in future Briefing emails. Additionally, once a task is complete, you can mark it as “done.” Similarly, you can mark documents linked in the Briefing email as either “not related” or “done.”

What If I Do Not Want To Receive Briefing Emails?

If you do not want to receive emails generated by the service, you can click Unsubscribe to opt-out of the service. Alternatively, you can ask someone with Administrative rights in Exchange Online to unsubscribe you.

Is The Briefing Feature Secure?

Briefing sends your daily email to you and only you. Further, your daily email only summarizes information that already resides in Outlook. Other team members cannot see any information about you that is not already in their mailboxes. Moreover, regarding privacy, Microsoft asserts that this complies with GDPR requirements.

Summary

Microsoft’s Briefing feature is yet another in a series of Office 365/Microsoft 365 Cloud-based tools that can enhance personal and team productivity. By reminding team members about items on their calendars and providing information that can help them to prepare for each of these events, Briefing offers great promise to ensure that team members are as productive as possible. And, as an optional service, team members who do not wish to utilize the service can opt-out. So, if you are an Office 365/Microsoft 365 subscriber with access to Exchange Online, be on the lookout for the new feature, as it is arriving soon.

You can learn more about Office 365/Microsoft 365 tools such as Briefing by participating in K2’s Microsoft Office 365 — All The Things You Need To Know.

Tommy Stephens

Excel's Ideas Feature - A Great Way To Summarize And Analyze Data

7/5/2020

Excel’s Ideas feature offers tremendous opportunities for you to summarize and analyze data automatically. With Ideas, you no longer need to write formulas or manually create formulas to get answers to your questions – Ideas does the work for you! In this tip, you will learn how you can take advantage of Ideas.

First, A Few Details

Unfortunately, not all Excel users will have access to Ideas. In fact, Ideas is available only in versions of Excel provided through Office 365/Microsoft 365 subscriptions or Excel Online. Therefore, if you are using a perpetual license of Excel, you will not have access to Ideas.

Second, while Ideas can work with large datasets, it does not presently handle large datasets. More specifically, Ideas does not currently work with a dataset greater than 1.5 million cells.

Finally, Ideas cannot analyze all types of data. For instance, if you have merged cells in your dataset, you will need to unmerge them before running Ideas. Also, if you have dates stored as strings, Ideas will consider them to be text. Therefore, you should use Excel’s DATE or DATEVALUE functions to convert the date strings into “true” Excel dates before running Ideas.

Summarizing Data With Excel's Ideas Feature

To quickly generate summarizations of your data using Ideas, begin by clicking anywhere in the dataset. Next, click Ideas on the Home tab of the Ribbon. Ideas will then generate a series of PivotTables and PivotCharts that summarize your data automatically. You will see these summarizations in the Ideas pane, which appears on the right side of your computer window. If desired, you can insert one or more of these summarizations into your workbook. To do so, click Insert PivotTable or Insert Chart in the lower, left corner of the summarization. Figure 1 below illustrates the types of automated analyses created by Ideas.

Figure 1 - Using Ideas In Excel

Getting Exactly What You Need From Ideas

In addition to generating default summarizations and analysis, Ideas supports natural language queries. These queries enable you to extract exactly the information you need from Ideas. For example, referring to the dataset shown in Figure 1, you could type “sum of order amount for employee id 4” into the Question box at the top of the Ideas pane. In turn, Ideas would create a PivotTable that summarizes the Order Amount field for the designated team member. Further, you could refine your query by entering “sum of order amount for employee id 4 in December” and Ideas would then filter the results so that they meet that criterion.

Summary

Excel’s Ideas feature is a significant step toward enabling artificial intelligence in Excel. However, it is also a practical tool to help us uncover trends, outliers, and other vital facts that we might miss otherwise with the human eye. Further, because of Ideas’ natural language query capabilities, we can “tell” the tool precisely what we want to know, and it will respond accordingly. Excel’s Ideas feature has a lot to offer, and if you have access to it, try it today.

Click here to learn more from Microsoft about Excel’s Ideas feature.

Interested in learning more about Excel? Consider joining us for a K2E Canada Inc. learning session that focuses on Excel. You can learn more by clicking here. Also, check out the video below that provides a step-by-step guide on how you can use Ideas.

Tommy Stephens

How I Work Efficiently From Home

5/3/2020

Working in a home office since 2005, I have made many changes to my setup in the interest of improving efficiency. Here are a few improvements I have made to my home office over time.

The Office

A room that is an office and not used for anything else. The separate room allows you to leave everything set up and provides the ability to leave the office. For myself, I find it hard to stop working and having it in a separate room does help.

For many, this is not a practical solution; however, finding a dedicated space can be a big help.
One more thing, you need a good office chair. Using the dining room chair is not going to be comfortable when sitting in it for hours at a time.

Monitors

Including my Surface Pro display, I use four monitors at my desk. Multiple monitors are a significant way to improve your efficiency. I find it slow and awkward to work on a single display.

In addition to improving the process of moving and viewing data, multiple monitors provide a screen to keep open the office chat program. The separate monitor makes it easy to stay in touch with co-workers. Glancing at the chat screen and a quick response keeps office communications flowing.

Collaboration Platform

A collaboration platform, such as Slack or Microsoft Teams, provides everyone with the tools to work together. These tools include chatting, audio calls, video calls, file sharing, file commenting, and file co-authoring. With all of this in a single application, it is very efficient for people to work as a team without being at the same physical location.

For us, this has been a great addition to our workflow. Simply ping someone in a chat and meet anytime quickly. Microsoft Teams collaboration platform can extend to people in organizations outside of your own. Recently, while working with another firm on a joint project, we used Teams to meet, collaborate, and share files. The collaboration was all done with the security of Teams allowing the outside organization access only to the project.

When meeting with people, video can make a considerable improvement in communication. Invest in a good quality webcam or use the one on your laptop or phone. To make it easier for others on the call with you, try to book meetings when the house is less active.

Most online meeting services today allow custom backgrounds. These backgrounds are a great option if you can’t set up a clean physical backdrop.

Mouse and Keyboard

A good quality ergonomic keyboard makes all the difference in your comfort. Combine the right keyboard and mouse, to make your day at the desk just that much better. Trying to use an inferior quality keyboard just slows you down.

Paperless

Scanning and storing documents electronic keeps the home office clean and organized without taking up valuable floor space with filing cabinets. We have been paperless since 2013 at K2E Canada Inc., and my accounting practice has been paperless since 2005. At this point, I can’t imagine working in a paper-based office.

Ward Blatch

<<Previous

Authors

Ward Blatch
Ward provides consulting and training services as the Managing Director of K2E Canada Inc. He joined K2E Canada in 2005 and is responsible for the Canadian operations of this international consulting group, which provides professional development technology education for accountants across Canada and the US. Ward lives in rural Nova Scotia and can be reached at ward@k2e.ca.

Tommy Stephens
Tommy is one of the shareholders in K2 Enterprises, affiliating with the Firm in 2003 and joining as a shareholder in 2017. At K2, Tommy focuses on creating and delivering content and is responsible for many of the Firm's management and marketing functions. Tommy resides in the metro Atlanta area. You may reach him at tommy@k2e.com.

Randy Johnson
Randy is a nationally recognized educator, consultant, and writer with over 40 years experience in Strategic Technology Planning, Accounting Software Selection, Paperless, Systems and Network Integration, Business Continuity and Disaster Recovery Planning, Business Development and Management, Process Engineering and outsourced managed services. Randy can be reached at randy@k2e.com

Bernie Smith
Bernie coaches businesses to develop meaningful KPIs and present their management information in the clearest possible way to support good decision making. As the owner of Made to Measure KPIs, he has worked with major organisations including HSBC, Airbus, UBS, Barclays, Credit Suisse, Lloyds and many more.

RSS Feed

Introducing STOCKHISTORY

Using STOCKHISTORY

Another Example of STOCKHISTORY

Summary

View a Video Tip That Demonstrates STOCKHISTORY

Spill

Spill Range Reference

Sort

SortBy

Filter

The Ever-Changing Technology Landscape and It's Impact on Learning Plans

CLOUD COMPUTING

INFORMATION SECURITY

SUBSCRIPTION-BASED SOFTWARE

EMERGING TECHNOLOGIES

Planning Your Technology-Focused Training

FOCUS ON MISSION-CRITICAL CONTENT FIRST

ONGOING MAINTENANCE

SECURITY TRAINING SHOULD BE MANDATORY

SKILLS ENHANCEMENT TRAINING

BECOME FAMILIAR WITH EMERGING TECHNOLOGIES

Where to Obtain Your Technology-Focused Learning

TRADITIONAL LEARNING OPTIONS

SOME ALTERNATIVE LEARNING OPTIONS

Summary

Word's Editor Feature

PowerPoint's Design Ideas Feature

Artificial Intelligence in Excel

Summary

The Need to Collaborate

Sharing Office Documents

Adding Comments and Notes to Documents

Using Teams to Collaborate

Summary

Technical Support is Ending Soon for Some Versions of Microsoft Office

Only Supported Versions of Microsoft Office Will be Supported When Connecting to Office 365 Services

Does This Change Mean You Won't be Able to Access Your Email Using Older Versions of Outlook?

Summary

LET Fundamentals

LET Benefits

A Practical Example of How the LET Function Can Simplify Your Formulas

Summary

Learn More About LET in this Video

Who Gets Briefing?

How Does This Feature Help Me?

What If I Do Not Want To Receive Briefing Emails?

Is The Briefing Feature Secure?

Summary

First, A Few Details

Summarizing Data With Excel's Ideas Feature

Getting Exactly What You Need From Ideas

Summary

The Office

Monitors

Collaboration Platform

Mouse and Keyboard

Paperless

Categories

Authors

Training & Education

Tech News

More