Category: Excel

Understanding Outlining

8/4/2020

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:

Click the File tab of the ribbon and then select Options.
At the left side of the dialog box click Proofing.
Make sure the Ignore Words in UPPERCASE check box is selected.
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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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

7/5/2020

0 Comments

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

0 Comments

Quickly Entering Dates and Times

7/4/2020

0 Comments

Here is a quick way to enter the current date or time into a cell. Select your input cell and press Ctrl+; (that's the semicolon symbol ). Very similar is entering the time by using Ctrl+: (that's the colon symbol), Ctrl+Shift+;.

These shortcuts will work when you are entering a formula. For example, let's assume that you want to have today's date as a string (within quote marks) in a formula. Key in your formula, up to the first quote mark. Then type Ctrl+; and the date appears in the formula. Type a closing quote mark, and you can continue with your formula.

Alan Salmon

0 Comments

Shortcut Key for Format Painter

7/4/2020

0 Comments

For those of you who love to use the Format Painter but are a fan of keyboard shortcuts, here are the shortcut keys to use. Excel doesn't provide a single shortcut key you can use for the Format Painter. You can, however, use the following steps:

Select the cell from which the format is to be copied.
Press Ctrl+C. This copies the cell to the Clipboard.
Select the cells to receive the format.
Press Shift+F10, S, R. This sequence displays the Context menu and selects the options to paste just formatting.

Alan Salmon

0 Comments

Duplicate Workbooks Opening

7/4/2020

0 Comments

Sometimes when you open an Excel workbook, you may find that Excel "replicates" the workbook. When it opens, two workbooks are displaying in the taskbar. Both contain the same workbook name, but one is followed by a 1 and the other followed by a 2.

This is a feature of Excel that allows you to open the same workbook in multiple windows. To do this on the View tab, select New Window. Each time you do this, the file name will have a number added to the end in the title bar. Saving a workbook with multiple windows open will result in all the windows opening the next time you open the file.

To have only one window for the file open the next time, first, close all the windows but one and then save the file.

Alan Salmon

0 Comments

Using Strikethrough Formatting

6/4/2020

0 Comments

Strikethrough is one of the character formats you can use within Excel. Strikethrough places a horizontal line through the middle of the character (or characters) to which the attribute is applied. To apply strikethrough:

Select the cell whose contents you want to be struck, or select the characters within a cell if you don’t want them all struck through.
Press Ctrl+Shift+F to display the Format Cells dialogue box.
Make sure the Font tab is displayed.
Select the Strikethrough checkbox.
Click OK.

Alan Salmon

0 Comments

<<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.

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

Understanding Outlining

Alan Salmon

Spell-checking Uppercase Words

Alan Salmon

Rounding by Powers of 10

Alan Salmon

Forecast Sheet and Forecast.ETS

Forecast Sheet

The FORECAST.ETS Function

Ward Blatch

Snap a Picture to Import Data into Excel

A Prerequisite, You Must Use the App!

Importing Your Data Into Excel is Easy!

Summary

Tommy Stephens

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

First, A Few Details

Summarizing Data With Excel's Ideas Feature

Getting Exactly What You Need From Ideas

Summary

Tommy Stephens

Quickly Entering Dates and Times

Alan Salmon

Shortcut Key for Format Painter

Alan Salmon

Duplicate Workbooks Opening

Alan Salmon

Using Strikethrough Formatting

Alan Salmon

Categories

Authors

Training & Education

Tech News

More