Category: AccountingWEB Excel articles

Jul 16

First Thoughts on Excel 2013

by David H. Ringstrom

 

The “customer preview,” or public beta version, of Office 2013 became available online today. I'll give you a quick read on what I found in my first spin through Excel 2013. I'll have much more to report in the coming months.



Microsoft hasn't yet confirmed a shipment date for the final release, but it's widely expected to be delivered late 2012 or early 2013.
As shown in Figure 1, the ribbon interface that was introduced in Excel 2007 is retained in Excel 2013. The user interface itself has been tweaked, with changes like blocky worksheet tabs and capitalized ribbon tab names ‒ e.g., “HOME” instead of “Home.”
Figure 1: Excel 2013's new user interface.



The File menu, shown in Figure 2, has been reworked as well.
Figure 2: Excel 2013's File menu.



At first glance, most new features seem to be focused on Cloud-based document storage and number crunching:
Quick Analysis – Right-click on a selection of cells to preview conditional formatting, charts, totals, tables, and sparklines. You can view each of these features on the fly or click the icon to apply the selected feature.
Flash Fill – This new feature on the data menu purports to generate patterns of data, but as of yet, I can't get it to produce any results.
Recommended Pivot Tables and Charts – These new features build pivot tables or charts on the fly from the source data that you select, saving you from having to build the features by hand.
Power View – This new add-in purportedly will “make better business decisions and create beautiful, interactive reports.”
Timeline – A feature for filtering dates interactively in pivot tables, pivot charts, and cubes.
Relationships – A new feature that will show how data from tables is linked together.



It's likely there are other changes beneath the hood, but the above items are what initially jumped out at me. This version of Office is tightly integrated with Microsoft's Cloud-based SkyDrive [4] storage platform, but documents can still be saved to a local disk.



Office 2013 will only be compatible with Windows 7 and later, which means Windows XP users will be relegated to Office 2010 or earlier. Although details are still emerging, Office 2013 should be compatible with touch screens, but you'll need to purchase a Windows-compatible tablet or PC to use this feature. As of today's press release, no iPad- or Android-compatible versions of Office 2013 are presently planned.
If you take Office 2013 for a spin, please feel free to post your comments and thoughts below.



 A previous version of this article first appeared on www.accountingweb.com .



About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Jul 16

First Look at Excel 2013

By David H. Ringstrom


The “customer preview,” or public beta version, of Office 2013 became available online today. I'll give you a quick read on what I found in my first spin through Excel 2013. I'll have much more to report in the coming months.


Microsoft hasn't yet confirmed a shipment date for the final release, but it's widely expected to be delivered late 2012 or early 2013.
As shown in Figure 1, the ribbon interface that was introduced in Excel 2007 is retained in Excel 2013. The user interface itself has been tweaked, with changes like blocky worksheet tabs and capitalized ribbon tab names ‒ e.g., “HOME” instead of “Home.”


Figure 1: Excel 2013's new user interface.


The File menu, shown in Figure 2, has been reworked as well.
Figure 2: Excel 2013's File menu.


At first glance, most new features seem to be focused on Cloud-based document storage and number crunching:


Quick Analysis – Right-click on a selection of cells to preview conditional formatting, charts, totals, tables, and sparklines. You can view each of these features on the fly or click the icon to apply the selected feature.
Flash Fill – This new feature on the data menu purports to generate patterns of data, but as of yet, I can't get it to produce any results.
Recommended Pivot Tables and Charts – These new features build pivot tables or charts on the fly from the source data that you select, saving you from having to build the features by hand.
Power View – This new add-in purportedly will “make better business decisions and create beautiful, interactive reports.”
Timeline – A feature for filtering dates interactively in pivot tables, pivot charts, and cubes.
Relationships – A new feature that will show how data from tables is linked together.
It's likely there are other changes beneath the hood, but the above items are what initially jumped out at me. This version of Office is tightly integrated with Microsoft's Cloud-based SkyDrive storage platform, but documents can still be saved to a local disk.


Office 2013 will only be compatible with Windows 7 and later, which means Windows XP users will be relegated to Office 2010 or earlier. Although details are still emerging, Office 2013 should be compatible with touch screens, but you'll need to purchase a Windows-compatible tablet or PC to use this feature. As of today's press release, no iPad- or Android-compatible versions of Office 2013 are presently planned.
If you take Office 2013 for a spin, please feel free to post your comments and thoughts below.




A previous version of this article first appeared on www.accountingweb.com .

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Jul 13

Is This Excel Worksheet Protected?

By David H. Ringstrom, CPA


Long-term Excel users are familiar with the prompt shown in Figure 1. In Excel 2003 and earlier, there were two ways to determine if a worksheet was protected. One way involved attempting to modify a locked cell, which would trigger the aforementioned prompt. The second approach involved choosing Tools, Protection, and looking for the Unprotect Sheet command, as shown in Figure 2. Excel 2007 makes this determination a little easier, as the Review tab displays an Unprotect Sheet command, shown in Figure 3, when you activate a protected worksheet.
Figure 1: A typical prompt that appears in Excel when a user attempts to modify a locked cell on a protected worksheet.



Figure 2: Multiple clicks are required to unprotect worksheets in Excel 2003.



Figure 3: You can tell from a glance at the Review tab if a worksheet is protected in Excel 2007.



Regardless, in Excel 2007 and earlier, you're required to activate each sheet in turn before you can turn off worksheet protection. In Excel 2003, this can require significant mouse clicks, whereas Excel 2007 streamlines the process somewhat since you can keep the Review tab active. Fortunately, Excel 2010 users have a much easier approach at their disposal.
As shown in Figure 4, Excel 2010 users can choose File, Info, and then determine at a glance exactly which sheets within a workbook are protected. Users can then turn off protection via the Unprotect links.
Figure 4: The Info pane in Excel 2010 provides a list of protected worksheets.



Excel 2010 users can also utilize the Review tab as shown in Excel 2007, but keep the Info pane in mind should you need to turn off protection on multiple worksheets within a workbook.
If you're still using Excel 2003, there's a way you can eliminate a couple of steps involved with protecting or unprotecting worksheets by following the steps shown in Figure 5.
Figure 5: A toolbar customization in Excel 2003 can streamline worksheet protection tasks.



A previous version of this article first appeared on www.accountingweb.com .



About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Jul 05

Excel Speed Tips: Do You Know the Tricks?

There is not one among us who would not benefit from using Excel more effectively.  When you discover helpful features (that were always there!) you can save significant time. Do you use the Table and Form features to easily sift through large data sets? Do you know the keyboard shortcuts that help you blaze through your work?  Do you avoid wrecking formulas by using the Edit versus Enter modes appropriately?

Click here to read the full text of the article at AccountingWEB.com.

Jun 28

Using Clip Art in Excel Charts

By David H. Ringstrom, CPA



Recently, I described how you can use Excel's Table feature to have charts expand automatically and to add a self-updating title (see “ReIated articles” below). I'll complete this three-part series by showing how you can enliven charts with clip art.
If you don't already have clip art you'd like to use, you can access thousands of free images on Office.com, such as an apple or an orange.



To create a chart in Excel 2007 and later, create a data set like the one shown in cells A1 through E3 of Figure 1. Next click on cell A1, choose Insert, Column, and then the first 2-D option. In Excel 2003, enter the data in cells A1 through E3, select cell A1, choose Insert, Chart, and then click Finish.
Figure 1: Creating a chart from a Table in Excel 2007 and later.



The next step is to associate a picture with a data series in your chart. As shown in Figure 2, in Excel 2007 and later, right-click on a data point or series, and then choose the corresponding Format command. Choose Fill, Picture, and then click the File button to choose an image. Click the Stack option, and then click Close.
Figure 2: Adding clip art to a chart in Excel 2007 and later.



In Excel 2003, right-click on a data point (or series), choose the corresponding Format command, and then click the Fill Effects button on the Patterns tab. Within the Fill Effects dialog box, click the Picture tab, select a picture, choose Stack, and then click OK twice.
Figure 3: Adding clip art to a chart in Excel 2003.



At this point your chart should look similar to Figure 4. Repeat the aforementioned steps for each data point or series in your chart, and the end result should look similar to Figure 5.
Figure 4: Chart with clip art added to one series.



Figure 5: Chart with clip art added to both series.




A previous version of this article first appeared on www.accountingweb.com .



About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Jun 22

Automating Chart Titles

 

By David Ringstrom



Recently, I described how you can use Excel's Table feature to have charts expand automatically. In this article, I'll show how you can further automate your chart with a self-updating title.
To create a chart in Excel 2007 and later, create a data set like the one shown in cells A1 through E3. Next click on cell A1, choose Insert, Column, and then the first 2-D option, as shown in Figure 1.
Figure 1: Creating a chart from a Table in Excel 2007 and later.



The next step is to add a title to the chart. To do so, click once on the chart to select it, and then choose Layout, Chart Title, and then Above Chart, as shown in Figure 2. To manually change the title, you can click on the title field within the chart and edit the text as desired. However, we can use a formula to make the title dynamic instead.
Figure 2: Adding a title to the chart.



To automate your chart title, use two worksheet functions together:
  • COUNTA – This worksheet function returns the number of non-blank cells in a given range.
  • INDEX – This worksheet function returns data from cell coordinates that you provide.
Use COUNTA to determine the number of non-blank cells in row 1. This assumes you won't have any data to the right of your source data, shown in Figure 1. If you've entered four months of data, then COUNTA will return the number 5, because there will be five non-blank cells in the row. You'll then use the INDEX function to return the word “April.” To do so, you'll tell the INDEX function to look across row 1 of your worksheet, and return data from the nth cell, as provided by COUNTA. The formula will take this form:
=INDEX(1:1,COUNTA(1:1))



You may wish to add additional narrative, such as the word “Sales.” To do so, you can use a technique known as concatenation. Although Excel has a CONCATENATE worksheet function, I use this approach instead:
=INDEX(1:1,COUNTA(1:1))&” Sales”
In essence, I use an ampersand to join the additional text to my formula. Such text must be enclosed in double quotes.
The aforementioned formula can be entered in any cell in your worksheet. I've chosen to place it in cell A5, just below my source data, as shown in Figure 3.
Figure 3: A simple formula can return the month of the latest data in your chart.



You're now ready to automate your chart title. To do so, click on the title of your chart, and then click in the Formula bar. Click on the cell that contains your title formula, such as cell A5 in this case, and then press Enter, as shown in Figure 4.
Figure 4: Chart titles can reference a formula in a worksheet cell.



To make the chart completely dynamic, click on cell A1, choose Insert, Table, and then press Enter, as shown in Figure 5. My previous article in this series describes this technique in more detail.
Figure 5: Excel's Table feature automates charts such that new data appears automatically.



Your chart is now dynamic. If you've followed all of the steps in this article, you should be able to add a new column of data for May and see the new data and title automatically, as shown in Figure 6.
Figure 6: New data appears on the finished chart, along with a self-updating title.



Note: There are a couple of caveats to be aware of with regard to automating charts. First, if you make the data into a Table before you create your chart, Excel may group the data. To change this, click the Switch Rows/Columns button on the Design tab. Secondly, when automating chart titles, you must reference a worksheet cell, and your formula reference must include a worksheet name. Thus, you can't click on the title and enter =A5 in the formula bar. You must instead use the form =Sheet1!A5.
Stay tuned, as my next article in this series will explain the technique I use to utilize clip art within charts.

 

 A previous version of this article first appeared on www.accountingweb.com .
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Jun 16

Excel Tip: Using Today() versus NOW()

 

By David Ringstrom, CPA
Many users rely on the NOW() function to return today's date in a spreadsheet. This is a perfectly valid purpose, but in certain cases NOW() can cause a formula headache.



The NOW() function returns both today's date, but also the current time as well. For instance, if it's 12:00 p.m. on December 21, 2012, the formula =NOW() in a worksheet cell will return 41,264.50. This is because 12/21/12 is 41,264 days subsequent to January 1, 1900.



The .50 portion represents half of a twenty-four-hour day. Thus, if you're comparing a static date value, such as 12/21/12 to the NOW() function, Excel will indicate that they don't match, because 41,264 isn't equal to 41,264.50.



The solution for comparing dates is to use the TODAY() function instead, since it returns the date portion only; for example, 41,264 for 12/21/12.



A previous version of this article first appeared on www.accountingweb.com .



About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Jun 16

Excel Tip: Pasting Text into Excel 2010

 
By David Ringstrom, CPA



From time to time, you might copy a list of data in text format from a webpage, report screen, or other source to the Windows clipboard. In such cases, Excel 2010 offers a hidden shortcut that makes it easier to break such data into columns.



With text data on the clipboard, click the lower half of the Paste button on the Home tab, and then choose Use Text Import Wizard.



This feature allows you to parse delimited data into columns based on a separator, such as tab, semicolon, comma, space, or a single character of your choice. Or, you can choose Fixed Width and manually place column breaks where you need them. This saves the step of pasting data into a worksheet column and then manually launching the Text to Columns wizard on the Data tab of the ribbon.




A previous version of this article first appeared on www.accountingweb.com .

 


About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Jun 16

Excel Tip: Data Entry Shortcut

By David Ringstrom, CPA



If you find yourself making repetitive entries into an Excel spreadsheet, press Alt-Down to view a drop-down list of previous entries in the current column.



Arrow down the list and press Enter to make a selection.



Keep in mind that this technique works with text only, not numbers, and your cursor must be in or adjacent to a contiguous block of non-blank cells.



A previous version of this article first appeared on www.accountingweb.com .


About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Jun 16

Excel Tip: Automatic Backup of Key Excel Workbooks

 
By David Ringstrom, CPA



From within the Save As dialog box of Excel, click the Tools menu, and then choose General Options.



Click the checkbox to Always Create Backup to have Excel automatically create an .xlk version of your file. For instance, if you save the workbook as Participant Hours Tracking.xlsx, each time you save the file Excel will create or overwrite a second workbook named Backup of Participant Hours Tracking.xlk.



The backup copy is only as old as the last time that you saved, so if you compulsively save every five minutes, your backup will always be five minutes old, but it does offer a bit of a safety net.



A previous version of this article first appeared on www.accountingweb.com .





About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Jun 01

Automating Excel Charts in Two Keystrokes

By David H. Ringstrom, CPA


Readers of a certain age will remember a television game show in which contestants would state “I can name that tune in five notes,” or some such number of notes. Along those lines, I'll show you how to automate an Excel chart with just two keystrokes. I'll first show you the typical way that most users accomplish adding data to a chart, and then I'll share the simple technique that eliminates this mundane need.



Let's say that you maintain a monthly chart, such as the one in Figure 1, where you periodically add another column of data. In Excel 2007 and 2010, most users carry out these steps to expand the chart:
Figure 1: Most users manually expand charts like this one.
  • Add a new column of data, such as for June.
  • Click once on the chart, and then choose Select Data from the Design tab of the ribbon.
  • As shown in Figure 2, change the Source Data range to be $A$1:$G$3, and then click OK. The month of June should then appear on the chart. Repeat this action month after month.
Figure 2: Steps involved in manually expanding a chart in Excel 2007 and later.



Power user tip: Typically pressing an arrow key within the Source Data field inserts an extraneous cell address. Press F2 to toggle to Edit mode, which means you can use the arrow keys to navigate within the field.



Alternatively, you can let the Select Data button molder. In Excel 2007 and 2010, click any cell within your chart's source data, press Ctrl-T, and then press Enter. Add a new month of data into the worksheet, and you'll see that the chart expands itself automatically, as shown in Figure 3.



Figure 3: Charts based on tables expand automatically.



Ctrl-T is the keyboard shortcut for the Table feature, which first appeared in Excel 2007. It's actually a revamp of the List feature that has long existed on the Data menu of earlier versions of Excel. Charts automatically adopt the dynamic nature of tables, which expand automatically when you add columns or rows of data.
Unfortunately this technique isn't available in Excel 2003 and earlier. The predecessor List feature doesn't interact with charts in the same fashion as the Table feature in Excel 2007 and later. However, if you're using a current version of Excel, you can use this keystroke process to instantly automate your existing charts.




A previous version of this article first appeared on www.accountingweb.com .

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

May 09

Microsoft Excel: Thirteen Double-Click Tricks

By David H. Ringstrom, CPA



The impact of single-clicking on a menu or object in Microsoft Excel is generally intuitive, but double-clicking sometimes reveals hidden shortcuts. In this article I'll discuss a baker's dozen of techniques where double-clicking can save you time and effort.
1. Skip the OK button: Unless you're clicking a checkbox, double-click on your choice within any dialog box in Excel to select that option and click OK simultaneously. For instance, press Ctrl-1 to display the Format Cells dialog box. Double-click on any format within the Number tab to apply the formatting and close the dialog box in one step.
2. Maximize file windows: Double-click on the title bar of Excel's Open or Save As windows to see more files and folders. Double-click the title bar again to restore the default window size. You can double-click on Excel's title bar or a worksheet window as well.
3. Split a worksheet: Double-click either of the split handles in Figure 1 to view two sections of the same worksheet at the same time. Double-click on a split to remove it.



Figure 1: Double-click the slit buttons to view two sections of a worksheet simultaneously.



4. Fill handle: Just about everyone knows that you can grab the little black notch in the right-hand corner of a cell and drag down to fill a range of cells. Double-click this Fill Handle instead to copy data or formulas down until a blank cell is encountered in the adjacent column. In Excel 2010 and later, this feature copies down to the bottom of the contiguous block of cells, meaning it can skip past blanks in the immediately adjacent column.
5. Lock the Format Painter: Most users click once on the Format Painter to transfer formatting from one cell or drawing an object to another. To apply the same formatting to a number of non-contiguous cells, double-click the Format Painter and then format as many cells as you wish. Press Escape once you finish with the Format Painter.
6. Rename a worksheet tab: Although you can right-click on a worksheet tab and choose Rename, double-click the tab to immediately edit the name.
7. Adjust column widths: Double-click the right edge of any column on the worksheet frame to automatically resize that column to fit the width of the longest cell entry. Or, select two or more columns and double-click the right edge of a column to resize multiple columns at once.
8. Collapse the ribbon: Double-click any ribbon tab in Excel 2007 and later versions to collapse or expand the ribbon interface, thereby giving you more (or fewer) rows onscreen.
9. Navigate a worksheet: Double-click any border of a worksheet cell to move your cursor in that direction until it encounters a blank cell or the worksheet frame.
10. Edit a cell: Double-click any worksheet cell to edit its contents in place rather than moving your mouse to the formula bar.
11. Identify Precedent Cells: This technique requires that you first turn off the Allow Editing Directly in Cells option under the Advanced section of the Options window in Excel 2007 and later. In Excel 2003, this option appears on the Edit tab of Excel's Options window and is labeled Edit Directly in Cell. In either case, once you turn the option off, double-clicking a worksheet cell will highlight the precedent cells that a given formula refers to.
12. Close Excel: Double-click the Excel logo in the top left-hand corner of any version in Excel (or the Office button in Excel 2007) to exit the program. You may be prompted to save any open workbooks.
13. Pivot Table Drill-Down: Double-click on any numeric value within a pivot table to create a new worksheet that reveals the underlying records that comprise that amount.



A previous version of this article first appeared on www.accountingweb.com .
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

May 04

Improve Your Excel Skills with this Free Excel Training

How are your Excel skills? Average? Enough to get by? Have you ever thought that with just a little training, you may be able to get your work done faster, and maybe reduce the errors that occasionally sneak in?

Click here to read the full text of the article at AccountingWEB.com.

Apr 30

Excel Tip: Calculating Straight-Line Depreciation

 
By David Ringstrom, CPA



The SLN feature in Excel enables you to compute straight-line deprecation for an asset. The function has three arguments: Cost, Salvage, and Life.


Cost is the original cost of the item, Salvage is the proceeds you expect to receive upon disposal, while Life is the useful life of the asset specified in years.


Thus, SLN would return $1,000 for a $5,000 asset with a salvage value of zero and a five-year useful life.



 A previous version of this article first appeared on www.accountingweb.com .


About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Mar 29

Excel Tip: Text Centering Trick

 
By David Ringstrom, CPA



Need to center a title or other data across your worksheet? Most users gravitate to the Merge Cells command, which can wreak havoc with many of Excel's features.


Instead, select the cells you wish to center text within, and then press Ctrl-1 to display the Format Cells dialog box.
On the Alignment tab, choose Center Across Selection from the Horizontal drop-down list.



A previous version of this article first appeared on www.accountingweb.com .
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link