K2E Canada Inc
  • Home
  • TRAINING
    • WEBINARS
    • ROAD TO EXCELLENCE ONLINE SEMINAR
    • SEMINARS
    • CONFERENCES
    • TECH SEMINAR
    • INSTRUCTORS
  • NEWSLETTER
    • Signup
  • ABOUT
  • CONTACT
  • BLOG
  • Home
  • TRAINING
    • WEBINARS
    • ROAD TO EXCELLENCE ONLINE SEMINAR
    • SEMINARS
    • CONFERENCES
    • TECH SEMINAR
    • INSTRUCTORS
  • NEWSLETTER
    • Signup
  • ABOUT
  • CONTACT
  • BLOG

How to Format Excel PivotTables for Even Greater Effect

10/11/2020

0 Comments

 
One of the unfortunate misconceptions regarding PivotTables in Excel is that you cannot format them to meet your specific reporting needs. Of course, the reality is that you can apply formats to your PivotTables to, in most cases, meet your exacting specifications. Read on, and in this article, you will learn just how easy it is to format your PivotTables for even greater effect.

Default Formatting in PivotTables

To begin, consider the excerpt of the PivotTable pictured in Figure 1. This raw report, although computationally correct, is difficult to read. For example, the Compact format makes the data in the first column challenging to follow.
Picture
Figure 1 - Excerpt of Unformatted PivotTable
Fortunately, you can change the layout of the report quickly. To do so, click Report Layout from the PivotTable Design tab of the Ribbon. Then choose Show in Tabular Form to change the report’s appearance to that pictured in Figure 2. In this layout, notice that the entries that were “nested” in column A previously now occupy individual columns, so they report appears less cluttered.
Picture
Figure 2 - PivotTable Which Uses The Tabular Layout

If you run Excel 2016 or newer, you can make the Tabular Form your default layout for all future PivotTables you create. To do so, click File, Options, Data, Edit Default Layout, as shown in Figure 3. Additionally, notice that you can make changes to other default settings for your PivotTables in that dialog box.
Picture
Figure 3 - Changing Default Settings For Future PivotTables


Disable Unnecessary Subtotals

In addition to the formatting changes outlined above, you may want to disable unnecessary subtotals in the body of the PivotTable. Often, subtotals contribute to a “cluttered” appearance of the data. To disable Subtotals, click on the PivotTable, and then click Subtotals, Do Not Show Subtotals on the PivotTable Design tab of the Ribbon. Upon doing so, the PivotTable will suppress Subtotals in the body of the report, as shown in Figure 4.
Picture
Figure 4 - PivotTable Without Subtotals


Repeat Item Labels in the PivotTable

In addition to the previous two customizations, consider repeating all the item labels in your PivotTable. This feature is especially helpful if you also choose to “collapse” the PivotTable, as discussed below.

To turn on the Repeat All Item Labels, again return to the PivotTable Design tab of the Ribbon. Then click Report Layout, followed by Repeat All Item Labels. As Figure 5 shows, this action fills the data in the Client, Client Contact, and Client Phone fields of the PivotTable, creating a format that many will find familiar.
Picture
Figure 5 - Repeat Item Labels In A PivotTable


Consider Using the Collapse Field Option

If your PivotTable report is particularly lengthy, you may also want to activate the Collapse Field option. Doing so will present a summarized PivotTable with the capability of drilling-in to the report for more details. In this illustration, let us “collapse” the data in the Client column of the summary. To do so, click anywhere in that column in the PivotTable and then choose Collapse Field from the PivotTable Analyze tab of the Ribbon. After doing so, you will be able to drill in on the report to see details, without being overwhelmed by all the data contained in the summarization.  
Picture
Figure 6 - Using The Collapse Field Option


Summary

No doubt, you can take advantage of other formatting options in PivotTables, including font, font size, colors, and others. However, most users do not struggle with applying those formats. On the other hand, many Excel users labor to control the volume of data presented in their reports. These same users also often face challenges with making the data easier to read. Addressing the four items outlined in this article – 1) establishing default formatting, 2) disabling unnecessary subtotals, 3) repeating item labels, and 4) using the collapse field option – you can quickly and easily format PivotTables for even greater effect.
You can learn more about PivotTables by participating in K2's Excel PivotTables for Accountants.
Also, you can learn more about establishing default settings for your PivotTables by clicking here.

You can see this tip in action by watching the video below.


Tommy Stephens

0 Comments

Excel's New LET Function Can Simplify Your Formulas

9/7/2020

0 Comments

 
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:

  1. 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.
  2. 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))
Picture
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

0 Comments

Finding Cells That Use Conditional Formatting

9/6/2020

0 Comments

 
There are times when you want to find cells that have conditional formatting applied to them. This is easy to do using the Go To feature of Excel. Here’s the steps:

  1. Press the F5 key to display the Go To dialog box.
  2. Click on Special. Excel displays the Go To Special dialog box.
  3. Click on the Conditional Formats radio button.
  4. Click OK.

You’re done. Excel will highlight all the cells in the current worksheet that have conditional formatting applied to them.

Alan Salmon

0 Comments

Creating Sheets for Months

9/6/2020

0 Comments

 
When you are creating new workbooks, you will often have a worksheet for each month of the year.  This is a very tedious process so here is a macro that will eliminate a lot of work.

The macro below does the following:

  • It first checks the names of the worksheets in your workbook
  • if they begin with the letters "Sheet" It then renames them to the months of the year.
  • If there aren’t sufficient sheets in the workbook, it adds sheets, as necessary, for each month of the year.

Sub DoMonths()
    Dim J As Integer
    Dim K As Integer
 
    For J = 1 To 12
        If J <= Sheets.Count Then
            If Left(Sheets(J).Name, 5) = "Sheet" Then
                Sheets(J).Name = MonthName(J)
            Else
                Sheets.Add.Move after:=Sheets(Sheets.Count)
                ActiveSheet.Name = MonthName(J)
            End If
        Else
            Sheets.Add.Move after:=Sheets(Sheets.Count)
            ActiveSheet.Name = MonthName(J)
        End If
    Next J
 
    For J = 1 To 12
        If Sheets(J).Name <> MonthName(J) Then
            For K = J + 1 To Sheets.Count
                If Sheets(K).Name = MonthName(J) Then
                    Sheets(K).Move Before:=Sheets(J)
                End If
            Next K
        End If
    Next J
 
    Sheets(1).Activate
End Sub

The final step in the macro is that it organizes the worksheets in the correct order for the months 1 through 12. If you have any other worksheets that do not begin with the word "Sheet", then these sheets end up at the end of the workbook, after the sheets for the 12 months.

Alan Salmon

0 Comments

Quickly Entering Dates and Times

9/6/2020

0 Comments

 
Excel provides two excellent shortcuts if you need to enter today’s date and time. To enter the today’s date, just select a cell and press Ctrl+; (that's the semicolon). For today’s time you do it by using Ctrl+: (that's the colon), which is really as Ctrl+Shift+;.

These keyboard shortcuts will work even when you are entering formulas. For instance, let's say you wanted to have today's date as a string (within quote marks) in a formula. What you need to do is enter your formula, up to the first quote mark. Then type Ctrl+; and the date appears in the formula. Type your closing quote mark, and you can continue with your formula.

Alan Salmon

0 Comments

Understanding Outlining

8/4/2020

0 Comments

 
Excel has a feature that allows you to outline your data. When you use this tool Excel analyzes your data and assigns different rows to different "levels". These levels can then be hidden or displayed, according to your requirements. An outline allows you to get a quick understanding of large amounts of data.

There are several ways to create an outline:

  • Inserting automatic subtotals. To do this select a cell in a data list, then click on the display the Data tab of the ribbon. Select Subtotal in the Outline group.
  • Letting Excel automatically outline your data list. Select the list that you want to outline and then select the Data tab of the ribbon. Click on Group from the Outline group, then click on Auto Outline.
  • Manually outlining your data list. Highlight the data you consider to be detail information and then display the Data tab of the ribbon. Click on Group from the Outline group and then click Group. Repeat this process for all the other detail information in the data list.

When your data is outlined, outline symbols will appear on the left side of your worksheet. You can display the different levels of data by using the mouse to click on the various symbols.

Alan Salmon

0 Comments

Spell-checking Uppercase Words

8/4/2020

0 Comments

 
Excel has a powerful spell-checker that does an excellent job. However, if your worksheets contain lots of acronyms, you'll find that Excel flags a lot of them as misspelled words.

To force Excel to ignore uppercase words in any spell-check you need to do the following:

  1. Click the File tab of the ribbon and then select Options.
  2. At the left side of the dialog box click Proofing.
  3. Make sure the Ignore Words in UPPERCASE check box is selected.
  4. Click on OK.

The only problem with this solution happens if you use all uppercase for section titles or for other special words. In this case, Excel still ignores them since they are uppercase. Make sure you change the setting of this check box based on the type of work you are doing in your worksheet.

Alan Salmon

0 Comments

Rounding by Powers of 10

8/4/2020

0 Comments

 
When you need to round a number to the nearest 10, 100, or 1000, you can utilize the ROUND function with negative values as the second parameter.

For example, if you have a value in cell B5 and you want to round it to the nearest 100, use the following formula:

=ROUND(B5,-2)

The effect of the -2 is to round the second digit to the left of the current decimal place. If B5 contains the value 12446, then the formula returns 12400. Similarly, if B5 contains 12472, the results will be 12500.

The same technique works to round to the nearest 10 by using -1 as the second parameter, or to the nearest 1000 by using -3.

Alan Salmon

0 Comments

Forecast Sheet and Forecast.ETS

8/1/2020

0 Comments

 

Forecast Sheet

Excel’s Forecast Sheet feature not only creates forecasted values that account for seasonality, but it also can provide confidence boundaries around the forecasted values and create forecasted values for multiple future periods. Using the data shown below begin by clicking in the data you wish to use as the foundation for your forecast sheet. The data requires a time line with a consistent interval and corresponding values.  Up to 30% of intervals can be missing and the Forecast Sheet will still be accurate.
Picture

Next, click Forecast Sheet on the Data tab of the Ribbon. In the ensuing dialog box (shown below), make any adjustments necessary for the time periods, confidence level, etc. and click Create to build the Forecast Sheet. 
Picture

Clicking Create results in a new sheet being created to the left of the data sheet with a copy of the data, the results of the forecast, and a chart of the forecast. This is a copy of the data and any changes to the data will not be reflected in the forecast. To make any changes to your selections in the dialogue above you will have to edit the formulas in the forecast sheet or run the Forecast Sheet command again. 
Picture


The FORECAST.ETS Function

The Forecast Sheet results above use the FORECAST.ETS as shown in the screen shot above. This function is an improvement over the FORECAST function which provided only linear forecasting capabilities. With FORECAST.ETS, you can now generate forecasts with exponential smoothing. The primary benefit to using the FORECAST.ETS function is that its exponential smoothing capacity allows you to create forecasts that account for seasonality in your data. To illustrate, consider the data below (with multiple rows hidden for presentation purposes). In it, a formula that uses the FORECAST.ETS function is computing forecasted sales for January 2021. Notably, because this formula employs the FORECAST.ETS function instead of the FORECAST function, it accounts for seasonality in the data. 
Picture
forecast_sheet_and_forecast.ets.xlsx
File Size: 32 kb
File Type: xlsx
Download File

Ward Blatch

0 Comments

Snap a Picture to Import Data into Excel

8/1/2020

0 Comments

 
Last fall, Microsoft announced forthcoming capabilities to take a picture of a data set using your Android-based or iOS-based device and import that data in Excel. That feature has now been fully rolled-out to both the Android and iOS platforms. In this tip, you will see just how easy it can be to get data into Excel by simply snapping a picture.

A Prerequisite, You Must Use the App!

To take advantage of this feature, you must use the Excel app for your mobile device. Fortunately, if have not already acquired the app, you simply visit your app store to access and download the tool. Of course, if you are using an Android device, you can find the app in Google Play; if you’re running an iPhone or iPad, visit the iTunes App Store from your mobile device. Keep in mind that the Excel app is free, although you will have an enhanced set of features and functions if you also have an Office 365 subscription.

Importing Your Data Into Excel is Easy!

To import data into the Excel app, begin by opening a blank workbook in the app. Then, just tap the Insert Data from Picture button highlighted in Figure 1 to open the camera on your device.
Picture
Figure 1 - Taking a Picture of Data Using the Excel App

Using the camera, take a picture of your data, trying to focus as tightly on the data range as is possible.  An illustration of the initial image taken by the camera is provided in Figure 2. 
Picture
Figure 2 - Initial Picture of Data for Converting to Excel

Next, crop the picture as tightly as possible by dragging the circles surrounding the image. The more closely you can crop the picture, the more accurate your data conversion will be and Figure 3 provides a sample of the cropped data.
Picture
Figure 3 - Cropped Data Ready for Conversion and Import

Immediately prior to converting the data into Excel, the app provides you with a preview that compares the initial image to how the data will appear in Excel. An example of this image is shown in Figure 4. To complete the process, just tap the Insert button in the lower, left corner of the app, as shown in Figure 4.
Picture
Figure 4 - Comparing the Cropped Image of the Picture to the Preliminary Data Results

Finally, as you can see in Figure 5, Excel inserts the data into the spreadsheet. Once inserted, you can work with the data just as if you had manually entered it into the app. Of course, if you have a OneDrive, OneDrive for Business, or SharePoint Online account and you save the workbook there, you can then open the workbook – inclusive of the imported data – using your desktop version of Excel.
Picture
Figure 5 - Data Imported into Excel from a Picture


Summary

As you can see, importing data from a picture into Excel is rather easy – just snap a picture of the data using the Excel app on your smartphone, crop the image, and Excel will do the rest of the work for you. This is but one way that Microsoft is easing the process of getting data into your workbooks. Others to consider are the Stock and Geography data types added to Excel 2019 and Power Query. We encourage you to stay on top of new features such as these so that you can work as efficiently as possible in Excel.

Tommy Stephens

0 Comments
<<Previous

    Categories

    All
    Automation
    Business Intelligence
    COVID 19
    COVID-19
    Cybersecurity
    Ecommerce
    Excel
    Internal Priorities
    KPIs
    LET
    Microsoft 365
    Microsoft Teams
    Office 365
    PivotTables
    Power BI
    Ransomware
    Security
    Tech Update
    Windows
    Word
    Work At Home

    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.

    Alan Salmon
    Alan Salmon is recognized as Canada’s leading analyst in the area of accounting technology. He has nearly 35 years of business, management systems, education and journalism experience, has a degree in Science and an Advanced Teaching Certificate from the University of Toronto. Alan has now retired from teaching and lives in Brampton, Ontario with his wife Nancy. He can be reached at alan@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

    RSS Feed

Training & Education

Webinars
Seminars
Conferences
Road to Excellence Online Seminar
Instructors

Tech News

K2E Canada's Tech Update Newsletter

More

Privacy Policy
About
Contact
K2E Canada Inc.  |  591 Highway 221  |  North Kingston, NS Canada B0P 1R0  |  (902) 200-9207
© 2020 K2E Canada Inc. ALL RIGHTS RESERVED.