By David H. Ringstrom, CPA Periodically, you may have a need to fill in gaps within an Excel spreadsheet. One way to do so is to manually fill in each cell, but in this article, I’ll show you three alternatives. First I’ll use a formula, then Excel’s Find and Replace function, and finally the often-overlooked …
Jan 23
Homeland Security Warns of Java Vulnerability
By David H. Ringstrom, CPA Computer security sites across the Internet lit up last week with the news that hackers are actively exploiting vulnerabilities in the ubiquitous Java software that resides on many computers. In a rare move, the United States Computer Emergency Readiness Team (US-CERT) advised all computer users to immediately disable Java within …
Jan 14
Using Collections in Excel to populate UserForm Controls
The following is programming code we frequently use to populate a drop-down list or listbox on an Excel UserForm with a unique list of items from a spreadsheet. Private Sub UserForm1_Intialize() ‘Creates a new collection Dim myList As New Collection ‘Determines number of rows to loop through numRows = Range(“A1”).CurrentRegion.Rows.Count ‘Optional – erases existing dropdown …
Jan 14
Write to CSV file
The following is programming code we frequently use when creating CSV files from Excel. Sub WriteToCSVFile() ‘Captures date/time stamp for appending to file name strTime = Replace(Format(Now(), “mm-dd-yy hh:mm”), “:”, “-“) ‘Prompts user to specify a file name strFile = Application.GetSaveAsFilename(“File Name ” & strTime, “*.csv,*.csv”, , “Specify File Name”) ‘Terminates routine if user clicks …
Jan 09
Hide and Unhide Multiple Excel Worksheets with Ease
By David Ringstrom, CPA It’s easy to hide worksheets in Excel, but unhiding multiple worksheets within a given workbook can be a tedious exercise. Users who don’t know otherwise are relegated to unhiding worksheets one at a time. In this article I’ll explain a technique that allows you to hide and unhide multiple worksheets at …
Jan 09
Resolve to Learn and Use 14 Excel Keyboard Shortcuts in 2014
By David Ringstrom, CPA It’s a new year, which brings the promise of fresh starts, and resolutions to work smarter. Keyboard shortcuts are one of the best ways to both save time and reduce wear and tear on your wrists when working in Microsoft Excel. In this article I discuss fourteen of the keyboard …
Dec 22
Verifying Dates within Excel UserForms
The following code is a routine we frequently use in our Excel projects to verify that the user has entered a date properly within a text box in a userform. In addition, dates such as 0704 get transformed to 07/04/yy where yy is the current year. Valid date inputs include 0704, 070412, 07/04/12, or 07/04/2012. …
Dec 17
Utilizing Excel’s COUNTIF Function to Break Ties
By David Ringstrom, CPA In a recent article I demonstrated how you can use the LARGE and SMALL functions to isolate the x largest or smallest values within a list. I then used the MATCH and INDEX functions to return the corresponding names associated with the values. However, if two items on a list share …
Dec 10
Identifying Largest and Smallest Values in an Excel List
By David Ringstrom, CPA Periodically, you may wish to rank a series of items within an Excel spreadsheet. Many users often rely on sorting data in ascending or descending order. I’ll describe an alternative that uses the LARGE and SMALL functions to create an ordered list of whatever you’d like to rank. You’re probably already …
Nov 30
Plug and Print
Click here to access Plug and Print. Plug and Print adds a dialog box to your spreadsheet that automatically lists every visible worksheet. Print all worksheets by choosing Select All, or selectively print one or more worksheets. You can also choose whether to preview before printing.
Nov 30
First Impressions of Windows 8
By David Ringstrom, CPA On October 26, 2012, Windows 8 officially became the latest version of Microsoft’s flagship operating system. With previous Windows upgrades, you could pretty much be up and running on a new PC quickly – well as long as you weren’t using Windows Vista. To me, Windows 8 feels designed to stop …
Nov 30
Microsoft Excel: Use Color to Identify Variances
By David Ringstrom, CPA Accountants are often given the mundane task of identifying variances that exceed specific tolerances. Rather than trying to eyeball columns of numbers, you can use Excel’s Conditional Formatting feature to make such variances leap out on the screen. I’ll demonstrate how you can use Excel’s conditional formatting to identify variances that …
Nov 30
Microsoft Outlook: Disabling the Send without a Location Prompt
By David Ringstrom, CPA Outlook often tries to be helpful and alert you when you’ve left the Location field blank within a meeting request. However, often the meeting location is implicitly known or isn’t necessary, such as for a phone call. In this article, I’ll demonstrate how you can eliminate the prompt shown in Figure …
Nov 30
Managing Protected View in Excel 2010/2013
By David H. Ringstrom, CPA As you migrate to Excel 2010 or 2013, you may sometimes encounter spreadsheets that open in a special Protected View mode. You can view – but not edit – such documents until you grant permission within an onscreen prompt. I’ll explain Protected View and how you can adjust this security …
Nov 29
Building an Amortization Table in Microsoft Excel
In this one-hour presentation for Lawline.com, David Ringstrom, CPA uses an amortization table exercise in Microsoft Excel to demonstrate numerous ways to use Microsoft Excel more effectively.