Chat with us, powered by LiveChat

Category Archives: General

Plotting Empty Cells

I received a call from my daughter yesterday. She was totally frustrated as she was trying to plot data for a class project that contained a lot of empty cells. I told her how to fix it and then learned that these suggestions would not work on a MAC. As I helped her figure how to do it, I tripped across a lot of information on plotting empty cells and realized that a lot of people struggled with this. So, of course, here is a post on how to handle plotting data when some of the data is missing.

Here is a simple example. Below is twelve months data on beverage sales. Almond milk sales did not begin until June. Soy milk proved unpopular and they stopped selling it in May. The company introduced goat milk in January and then decided it needed more work and reintroduced it in November.

Let’s start with looking at the chart. Right off the bat, you can see there is something wrong with the top gray line for organic milk as there is a break in the line. Cider looks to be starting in March even though there is January data. January goat sales do not even display on the chart.

First, sometimes people want to add zeroes. Lets’ see what it looks like if I do that for February cider sales.

Well, now the January sales show but look at that plunge.  Adding zeroes is not recommended. Let’s see what alternatives there are.

In this example, below I have replaced the 0 with #N/A.

As you can see, it smoothed out without that excessive plunge where it was trying to display a zero.

Let’s try the same thing for the goat milk sales line.

Hmm.. not as perfect as it look as though. Goat milk was sold all year but better than what it was. Also, let’s face it, no one wants a lot of error values in their spreadsheets.

Another alternative that is the best, I think, is just show the empty cells as gaps or connect data points with a line.

To do this, right-click on the data in your chart and select Data…

Click on Hidden and Empty Cells.

If you look at the goat milk, when the cells are blank and I use Connect data points with line, it resembles the #N/A version but at least I do not have #N/A errors throughout my spreadsheet.

So, really you have a choice between showing gaps or connecting the lines.

I was reading Jon Peltier’s blog and he mentioned that Microsoft had updated the Hidden and Empty Cell Settings to handle #N/A. So, of course I immediately updated my Excel version and the new update suddenly included the Show #N/A as an empty cell. If you do not see that option, then you either have an earlier version of Excel or you need to update.

So, to finish this off and see how it looked, I put #N/A in all the empty cells, and then selected Connect Data points and put a checkmark in Show #N/A as an empty cell.

The results are below.

So, if you do have #N/As in cells, that option is a nice workaround.

Jon Peltier goes into much more detail and shows how the different choices look in a variety of chart formats so if you are looking for more information on this, I recommend you stop by Peltier Tech Blog.

And for you MAC users, if you right-click and Select Data…, you should see a similar dialog box.

Hopefully, my daughter’s project turned out well. For more information on computer software tips and tricks, see the full catalog of self-study CPE products.

Fill & Sign Forms-Adobe Acrobat DC

Fill and Sign PDF Forms

  • Open the PDF that you want to fill in and sign using Adobe Acrobat DC.
  • Click the Fill & Sign tool located in the right hand pane.
  • Click in a field and start typing.
    Adobe may suggest responses based upon previously entered data or stored information.
    To accept, Adobe’s suggestion, use your cursor to highlight it and then click to auto-fill the field with the suggested data.
    All your new responses will be saved and will appear as suggestions next time you fill out a form.

Checkmarks on the form

  • If you need to add a checkmark, hover over the checkmark square area and click once. Adobe will add and resize the checkbox.

Add A Signature

  • To add your signature directly into the form, click the Sign icon in the top toolbar.
add a signature adobe

  • Click the Add Signature.

You can do one of the following:




1.Type your name where it says “Type your name here” and then choose one of the signature styles.2. Draw your signature using your mouse in the “Type your name” area

3.Upload an image of your actual signature.

  • Once you have made your choice and created the signature, click Apply.

The signature is attached to your cursor, so move your mouse to the proper location and click once to place your signature.
You can click and drag the left side of the box if you need to resize it a bit. You can resize by dragging the handle on the  bottom right of the signature box where the blue dot is or you can use the font icons at the top of the contextual menu.

A warning box will display, telling you that when you save a form with a signature or initials, you can no longer edit the existing form fields so make sure that everything is the way you want it before you the save the form.

Send A Copy

In the upper right of the screen, there is a Send a Copy icon.  If you click that, it will request that you sign in with your Adobe id and then it will allow you to send this non-editable copy to others by generating a link which  you need to post into an email.


Let’s Get Rid of =GetPivotData


If you use pivot tables a lot, then you have run into  the GetPivotData formula. The other day, I was trying to create a formula referencing some pivot table cells and the GetPivotData formula popped up when I did not want it to. Today, I read Debra Dalgleish’s tweet on the same topic and realized that it is a problem for a lot of people. So, I thought some of you might be interested in learning how to get rid of GetPivotData.

GetPivotData has a lot of uses, however, if you want to create another formula in the column next to a pivot table and refer to a cell in the pivot table then you know what happens. First, you get a long long formula referencing the pivot table and the cell which is okay.

getpivotdata formula

But then, you copy it down and suddenly, you have the same answer displaying in every cell.

This drives me nuts and I am sure some of you are shaking your head yes and agreeing with me.




If you do not use GetPivotData and want to remove it all you need to do is click in the pivot table to select it and then do the following:

  1. Click the drop down arrow under Options on the Excel ribbon.






2. Click Generate GetPivotData command and turn it off.
If you do not see the Options choice, make sure your cursor is in the pivot table.

This turns the formula GetPivotData off for all future spreadsheets. So, if you do want to use it at a later point in time, you will have to go back in and turn it back on.

Adding VLookup as a Control for Your Month-End Workpapers


When closing for the month, many use an Excel workbook to reconcile balances to the general ledger.  As the general ledger changes, do you have a process in place to easily determine that balances previously reconciled still tie to the general ledger?  If not, using Vlookup on each reconciliation worksheet provides a quick and easy way to accomplish this control.

Usually, the month-end balance from a reconciliation worksheet is at the bottom of the worksheet.  Below this balance, you can enter the associated general ledger account number to be used in a Vlookup formula.  After the account number and Vlookup formula have been entered in each workbook, all you need to do is cut and paste the most recent general ledger balances into a separate worksheet.  The Vlookup will display the most recent balance and you can use conditional formatting to highlight whether the two balances are within a tolerable error limit.

The example below assumes that the AR Trial Balance has been created and the total needs to be compared to the general ledger.  There is a worksheet labeled “Accounts Receivable” and a separate worksheet labeled “General ledger”.

The general ledger account for accounts receivable has been entered at the bottom of the Accounts Receivable worksheet and a Vlookup formula is used to compare this total to the amount in the General Ledger worksheet.

vlookup as control 1








The Vlookup function is searching for a worksheet titled “General ledger” in the same workbook.  The key to making this work is that you never delete this worksheet.  When the general ledger changes, simply paste the new general ledger data over the old data in the same worksheet.

For Vlookup to work, the lookup value, in this case the general ledger account number, must be in the leftmost column of the General Ledger worksheet (In this example, column A).  The general ledger data is in columns A and B and the account balance is in column 2 (which is the B column).  The FALSE range lookup value is used to indicate an exact match.  The example general ledger is shown below.









Why use the entire columns of A and B?  That way, if general ledger accounts are added, we’re not constrained by a limited range.  This is important as we want to paste over this data again and again without using new Vlookup formulas each time.

To calculate any differences, subtract the general ledger balance from the reconciliation balance.  Use the ABS function in the formula since we just want to know the absolute amount of a difference and do not need to know whether it is positive or negative (ABS provides the absolute value of a number).








In this case, the difference is 0.  However, we want a way to highlight a difference if it exceeds a tolerable error limit.  Assume that we want to highlight any change greater than $100 and, if the difference is greater than $100, highlight the cell in yellow.

Select cell B13 and then select the Conditional Formatting button on the Excel Home ribbon.  From the Conditional Formatting options, select “Highlight Cell Rules”, then “Greater Than”.

conditional formatting vlookup












Once “Greater Than” is selected, you have the opportunity to input both an amount and highlight colors.  In this example, our amount is greater than 100 and we’ll use yellow fill with a yellow text to highlight the difference.  Note that we used an absolute value since the difference could be either greater than 100 or less than 100, depending on whether the general ledger amount was more or less than then the reconciliation amount.  Having only one value makes the Conditional Formatting comparison much easier.

Vlookup and conditional formatting









Press OK.

Since the difference is not currently greater than 100, there should be no yellow highlight.  If the general ledger balance is changed to 31,500 (a difference that is greater than 100), the difference is now highlighted.


With this Vlookup control in place for each month-end reconciliation worksheet, you can copy and paste the new general ledger data into the General Ledger worksheet and the new balances will update.  Any differences that exceed a tolerable error amount will be highlighted and you can simply scan each worksheet to determine if a difference exists.

This process is much easier to implement if all month-end account reconciliations are in one workbook.  That way, the general ledger data only needs to be pasted once in the General Ledger worksheet each time the general ledger is updated.

CPASelfStudy has several in-depth courses on Vlookup and Conditional Formatting.  If you want more information or other productivity ideas, please check out our Excel course offerings.




Economic Nexus – South Dakota Supreme Court Case

economic nexus

Economic Nexus

Update: Looks like many states are now moving forward with out-of-state sales tax.  My guess is that many states are going to set minimum revenue amounts so the small entrepreneurs and small EBay sellers won’t be impacted. Let’s hope.

8/2018- Here is the latest update and some of the state discussions going on:

6/2018 I just received the following information  from the Indian Department of Revenue:

” INDIANAPOLIS— The Indiana Department of Revenue (DOR) is announcing next steps after the U.S. Supreme Court issued its landmark decision in South Dakota v. Wayfair, Inc on June 21, 2018. 
Out-of-state retail merchants with annual gross revenues from Indiana sales exceeding $100,000, or 200 or more separate Indiana transactions will need to register and remit Indiana sales tax.  Pending resolution of a declaratory judgment action filed in 2017, DOR will begin enforcing Indiana’s economic nexus law on October 1, 2018, on a perspective basis. The Wayfair decision overturned the Court’s 1992 ruling in Quill Corp. v. North Dakota, which required a retail merchant to have a physical presence in a state to be subject to state sales tax registration and collection requirements.Indiana’s existing “economic nexus” law, effective July 1, 2017, is found at IC 6-2.5-2-1(c), and provides that a retail merchant that does not have a physical presence in Indiana shall collect the gross retail tax on a retail transaction made in Indiana if certain threshold requirements are met.
The law, which was challenged in the fall of 2017 and remains subject to a pending declaratory judgment action regarding its constitutionality, is directly impacted by the outcome of the Wayfair decision.Remote sellers seeking to comply with the laws of multiple states (including Indiana) should register with the Streamlined Sales Tax Registration system at Remote sellers seeking to comply with only Indiana’s economic nexus law should register through the online portal, INBiz, at”


In 2016, the state of South Dakota established an economic nexus standard for sales tax.  This standard requires sellers who meet certain revenue or transaction thresholds to collect and remit sales tax to the state even if they have no physical presence in South Dakota.  In September of 2017, the South Dakota Supreme Court held the state’s law to be unconstitutional.  However, in January of 2018, the U.S. Supreme Court agreed to hear the case with a ruling expected in the summer of 2018.  The case title is South Dakota v. Wayfair, Inc.

Since the 1992 U.S. Supreme Court case, Quill v. North Dakota, the standard for collecting and remitting sales tax to a state has been a physical presence in the state.  This physical presence, or nexus, is created by having facilities in the state, employees in the state or even sales personnel travelling to the state.

On-line sellers have benefitted from the physical presence requirement.   By being able to sell goods in states where they have no physical presence, on-line retailers have escaped the need to collect and remit sales tax in those states.

If South Dakota prevails in the case, the physical presence requirement could be replaced with economic nexus which is measured by a retailer’s revenue or number of transactions in a state.  This would require on-line retailers who were not previously required to collect and remit sales tax to do so.

The legal issue with the Quill case was the regulation of interstate commerce.  The ruling was that only Congress has the power to regulate interstate commerce and state action cannot restrict interstate commerce.  If the U.S. Supreme Court relies on legal precedent, it may keep the physical presence standard.  It may also recognize the realities of an internet economy where the physical presence standards could be construed as outdated.  Another option is to say that only Congress can decide the issue.

The stakes are high for both states and retailers.  Alabama and Washington are other states with an economic nexus standard.  Colorado has been trying to pass economic nexus and finally settled on requiring all Ecommerce companies with Colorado sales to submit a list of those sales to Colorado and to notify purchasers that they must pay Colorado use tax.  Idaho, Illinois, Louisiana, Mississippi, Nebraska, Oklahoma, Utah, Rhode Island and Vermont have also introduced legislation to create economic nexus.  If the court rules in favor of South Dakota, expect many more states to enact economic nexus standards.

This will be an interesting case to watch.

Find Files Easily

Are you an organized person? Is everything at your fingertips? If so, good for you. I, unfortunately, am a bit more unorganized and can spend quite a bit of time looking for a specific Excel file. I have found that adding keywords to a file when I create it, can help me locate it later if I don’t remember the name file. It is very easy to do.

use keywords



Click on File and then select
Click the Properties drop-down arrow located over on the far right side of the screen and then select Advanced Properties.

Click on the Summary Tab if necessary and enter keywords that relate to the file.  Click OK.

keywords in file













To search, simply go to the Windows Explorer window and in the search box, located on the far right of the window, type in one of your keywords.  Your file should display. You can also search on author and title as well.

search with keywords

Lots of options to find your file.

Passwords & Lucy the Retriever

Tips for Strong Passwords

Do you use strong passwords?  Are you sure?  They are the key to keeping your information safe and secure.  Since strong passwords are so important, a basic understanding of what constitutes a difficult to hack, strong password is essential.

A strong password should be long.  It should not be comprised of one word or a word followed by digits, dates or special symbols.  It really shouldn’t be an easy number combination like 123456.  Names, places and individual words in any dictionary should be avoided.

Why? Password hackers use dictionaries and names first.  Then, they tack on digits, dates and special symbols in an attempt to crack a password.  By the way, they use dictionaries in all languages, so don’t become complacent with your easy password in French or any language other than English.  Hackers will use a computer to accomplish this brute force hacking technique, so each attempt only takes milliseconds.  Easy word and numeric combinations can be cracked in seconds to minutes. has a password checker that provides an estimate of the amount of time it would take a brute force attack to crack a password.  Here are some estimates from this site:

1234 – is a top 1000 password.  It will be cracked in 0 seconds

123456789 – also a top 1000 password.  It will be cracked in 0-10 seconds depending on computer speed.

ILOVEYOU – Still a top 1000 password, but longer.  Depending on computer speed, it will take 0 seconds to 35 minutes to crack.

Here are some strategies for a strong password:

  • Pick a long phrase
  • Substitute certain letters with characters or numbers

Using the password checker from, let’s break down a phrase word by word to see how much more difficult it is to crack with the addition of each word or symbol.  The phrase will be “My Golden Retriever is Lucy”.


  1. My – 0 seconds
  2. MyGolden – 1 second to six days, depending on computer speed
  3. MyGoldenRetriever – 573 million to 573 trillion years
  4. MyGoldenRetrieveris – 2 trillion to 2 quintillion years
  5. MyGoldenRetrieverisLucy – 11 quintillion to 11 septillion years

As you can see, longer is better.  To make this phrase even stronger, substitute a number of symbol for 1 or two letters.  For this example, let’s use an @ sign for “G” and a 4 for “R or r”.

  • My@olden4etrieve4isLucy – 874 septillion years to 874 nonillion years.

That’s a pretty strong password.  It must be as I don’t even know how many zeros are in a nonillion.


Another strategy that may be used is to pick a phrase and only use the first letter of each word in the phrase.  Then, substitute numbers of special characters for certain letters.  Continuing with the above example, we’ll make the phrase a little longer.  The phrase will be:

  • My Golden Retriever is Lucy and she is a good dog

When we only use the first letter of each word, the password becomes MGRiLasiagd.  This password alone is not very strong.  According to the password checker at, the time to crack it is shown below.

  • MGRiLasiagd – 21 hours to 29 thousand years, depending on computer speed.

However, if we substitute a ! sign for each “i” and a @ sign for each “a”, the password becomes much stronger.

  • MGR!L@s!@gd – 40 years to 476 million years depending on computer speed.

A longer password is a stronger password.  The preferred method is a long phrase with the substitution of numbers or special characters for certain letters in the phrase.  Using the first letters of a phrase and substituting numbers or symbols for certain letters is not as preferable nor as strong as the use of a long phrase.  However, it can make an effective password (40 years is still a long time).

The key takeaways are to make your passwords long and don’t use one-word, easy passwords.  Never, ever use a password like “abc123”.  It’s easily guessed and can be cracked in seconds.


DockDogs at Fair

dockdogs- state fair

We went to the  Indiana State Fair this weekend and had a blast as usual. It is a family tradition.
So much to see and do and eat.

  • Ducks, Goats, Cows, 4H Exhibits, Scavenger Hunts, Gardens, Soybeans, Corn hole, Giant Pumpkins, Tractors, Train
  • Dairy barn milk shakes, Honey Sticks, Corndogs, SweetTea, Ribeye Steaks, Roasted Corn on the Cob (my fav)

We even saw a pig farm via Virtual Reality Glasses which was very interesting.
The highlight this year though were the Dock Dogs.
Dogs competing to see who can jump the furthest over a pool of water to get their toy.
This is a very short video of the first dog competing. A whippet was the winner jumping almost 30 feet!
Click on the link below to see the first dog compete – The video may download to the bottom of your screen.

dockdogs2017.mp4   (4 seconds)

Here is a link to more information about them -on the State Fair website.Kelsey on tractor at fair

Joe looking at garden at fair

Sort Left to Right

Who knew how many different ways there are to sort?

Let’s talk about changing the Sort Option Orientation.

Typically you don’t have to change the orientation of sorting as the default works just fine. However, everyone in awhile, you have that one spreadsheet that has a mind of its own.
I have a sales report that is generated every month and the columns are never in the correct order. So, I would cut and paste every month to get everything to display the way I wanted, however, it was  very time-consuming and annoying.

Below are the steps to do this in a more efficient manner and forget all about that cutting and pasting.

Insert a column above your column headings and number them in the sequence you want them to appear.
Then select Data>Sort.
In the Sort Dialog box click Options and select Sort left to right.
Voila- your columns are sorted in the way you want them and it took you all of 30 seconds.

sort left to right

The default order is Smallest to largest.

Notice that you can also sort by Case Sensitivity as well.

This is a tip from my Must Know Excel Tips Tricks and Tools for CPAs course.


Vlookup vs Index Match -Which is Better?

Chris Chau asked 27 Excel MVPs and community experts their preference and why.
Check out the results. Which do you prefer?vlookup

If you want more information after reading Chris’ blog, check out my course Become a Lookup Expert.
It covers LOOKUP, HLOOKUP,  INDEX MATCH and work-arounds for people who love VLOOKUP.


VLOOKUP vs INDEX-MATCH: 27 Excel experts share their opinions


Top 100 CPA Blogs And Websites For Accounting & Tax Pros

Top 100 CPA Blogs 
top 100 cpa blogs

We are honored to be included in the Top 100 CPA Blogs And Websites For Accounting & Tax Pros. say that these are the  “The Best CPA Blogs on the Planet“.

So, if you are looking for information on some topic this may be a great place to start. I know I have already identified a few blogs that I plan to spend some time reading.

Take a look when you get the chance.  Just click on the award picture and it will take you to the site. Of course, since you are here definitely check out my site and my sister site as well.


-Professional Ethics- CPA Case Study

Professional Ethics – A Case Study for CPAs in Business

CPAs in business have unique threats to ethical compliance. Many times, these threats come from their employer. This makes for a challenging environment when confronting a superior over an ethical quandary as it could result in the dismissal of the CPA from his/her employment or result in the CPA resigning from his/her employment. Maintaining professional ethics can be difficult. While the loss of a client is important to the CPA in public practice, the loss of the sole source of income of the CPA in business is much more frightening and impactful. This makes compliance with professional ethical standards challenging in difficult situations. In some situations, it can be a choice between ethics and providing for your family.

Ethics - Decision Time

Ethical dilemna

Therefore, being reminding of the compliance threats and compliance obligations is of utmost importance to the CPA in business. In using case studies, some cases provide a clear answer. Stealing is wrong…Period. However, there are many situations where the answer is not as clear. These situations present a quandary which must be resolved.
One such case study follows.
As the controller of a small distributor and a CPA, you are closing the books for the year. The owner, who is well past retirement age, has announced that a buyer for the business has been secured. Once the sale transaction has been completed, the members of senior management, including you, the controller, will receive a significant bonus. This bonus will be the equivalent of 150% of one year’s salary.
While reviewing the balance sheet, you notice that there are a number of expensive inventory items that are aged. Usually, inventory turns about once every 90 days, and these have been on the books for almost a year.
You approach the VP of Operations with your concern. He tells you that a competitor had devised a better product at a much lower cost. This product was unsaleable at any price. However, due to the cost of this product, writing it off would be a material adjustment and would certainly impact the purchase price and may put the entire sale in jeopardy. He says that it would be in everyone’s best interest to ignore it. “The buyer did their due diligence. If they missed it, it’s their problem, not ours.”
You bring the situation up to the owner. He says, “Don’t screw this up. I’ve worked a lifetime for this deal and I’m paying you a lot of money to make it happen.”
You know that everyone will be upset if the sale is not consummated or the bonus amount is reduced. Personally, you want the bonus. While you can always find another job, the bonus will buy a new car and pay off your student loans. But, you also know that the inventory is not properly stated and this will be determined at some point in time. You can always say that you just missed it. After all, you’re just the controller, not an operations guy.
What should the controller do?
Ethical Core Foundations to consider
Integrity and objectivity – These core foundations require the CPA to be honest, unbiased, not be influenced by personal prejudices and not subordinate judgment to personal gain. This case presents what is right – to value the inventory properly by writing it off or using a valuation allowance, versus ignoring the facts, as discovered, for personal gain.
Due care – Requires that the standards of the accounting profession are upheld at all times. Ignoring a known fact does not uphold the standards of the profession.
Threats to Ethical Behavior
The threats in this case are self-interest, undue influence and advocacy. The self-interest threat manifests itself in the desire to obtain the promised bonus. It is a significant amount of money. Undue influence come into play in the form of implied or actual coercion from the owner and company officers. They also have a significant stake in the success of the transaction. Their self-interest may result in coercing you.
Since there are no company safeguards noted in this case, the only possible safeguards are those created by the profession. A company safeguard would be a board of directors with whom a concern may be shared or an audit committee. The owner of the company has already made his wishes known. The only safeguard in this instance is your training in ethical behavior.

Code of Professional Conduct Rules
The AICPA Code of Professional Conduct addresses Integrity and Objectivity, stating that a member shall maintain objectivity and integrity, shall be free of conflicts of interest, and shall not knowingly misrepresent facts or subordinate his or her judgment to others. The Code also requires the general standard of Due Professional Care which requires that the CPA use reasonable care and diligence in the performance of work. The Accounting Principles Rule would also require that inventory be properly valued in accordance with GAAP. Lastly, negligence in the preparation of financial statements and records is an Act Discreditable.

professional ethics for cpas

Professional Code of Conduct

Example Thought Process
It is pretty clear that knowing there is an inventory issue and not recording the appropriate adjustment violates the core foundations of the CPA profession as well as many specific rules applicable to the CPA in business. You can try to tell yourself that you can plead ignorance. However, that would not be ethical. In this case, the ethical path would be to insist that the inventory adjustment be made. If this is refused, then you should disassociate yourself from this entity. While a big bonus sounds very nice, the money is fleeting. Discovery of unethical behavior can ruin your entire career.
For a variety of cases discussing the ethical obligations of the CPA in business, try the Ethics for CPAs in Business course at

– Privacy of Tax Return Data

Tax Return Data – Privacy Issues

I read an interesting article the other day on the privacy of your tax return.
Everyone, of course, needs to be aware that your tax-return information can reside in many places, whether you are doing just your own taxes or those of others.
The IRS share information with state tax agencies, law enforcement and others, which is sometimes required by law, other times not. However, it says that it tries to limit sharing to the relevant needed information.
As you know, all tax preparers, including CPAs who prepare and sign returns for clients, have strong privacy and ethics rules. Many state boards and the AICPA specify a minimum number of years’ tax return information must be maintained but there is little discussion concerning a maximum period.
If you file electronically, the files are transmitted to the IRS by the use of outside firms such as Intuit (Turbo Tax) and H&R Block or commercial firms such as Thomson Reuters.
According to the IRS, different transmissions have different retention policies but again no limit on how long these tax software companies and others can retain the information. A good question to consider, is if you change software vendors, when does that first vendor stop holding onto the data and what do they do with it?
It doesn’t sound like that big a deal but given the treasure trove of data and information and the recent success of so many cyber thieves, it is something to consider. So, where does that leave you?

Privacy- man on desert island

man sitting on a deserted island by himself.

Honestly, on a desert island by yourself. Rules on data breaches and data privacy are generally covered by state rules and regulations and there are a lot of differences within some states. Some State Boards of Accountancy discuss deliberate privacy violations, in their ethics rules, but the area of retention by third parties, electronic transmissions and data breaches is just beginning to be discussed.
Just food for thought – if you do taxes for clients, you may want to explore your state rules and regulations a bit and understand your responsibilities.

New Lease Accounting Standard

ASU 2016-02

lease accounting standards

a binder with the word leasing on it.

In February, 2016, the FASB released its new lease accounting standard, ASU 2016-02.
This standard changes most elements of lease accounting for lessees and practically eliminates the use of those leases currently classified as operating leases as an off-balance-sheet financing vehicle, except for short-term leases (those with a term of one year or less).

Under the new standard, leases with a term of greater than 12 months will require the recognition of a right of use (ROU) asset and a lease liability. The asset and corresponding liability will be initially measured by the present value of the lease payments. The discount rate used in this calculation is the rate implicit in the lease or, in the absence of an implicit rate, the lessees incremental borrowing rate.
Leases will be classified as either a finance lease (similar to today’s capital lease) or an operating lease. For finance leases, the ROU asset will be amortized but interest expense on the lease liability must also be computed. This will result in the recognition of both interest expense and amortization expense. For operating leases, the ROU asset will be amortized on a straight line basis, resulting in only lease expense. In both cases, the corresponding lease liability will decrease as payments are made.
Implementation of this new standard for public companies will be those years after December 15, 2018. For non-public companies, the standard is effective for years after December 15, 2019.
The IASB also published new lease accounting standards in January, 2016. The IASB adopted an approach that requires lessees to account for all leases as finance leases, except short-term leases. Check out our new course by Steven Bragg – Accounting for Leases.

Formatting Alternate Rows

Formatting alternate Rows

Thanks to Diana Lawless for sending in this Excel Format painter icontip.

Using the Shift key and the Format Painter icon together allows you to format an entire worksheet quickly. If you are formatting alternate rows or columns quickly and do want not mess around with Conditional formatting rules this is a great shortcut.
Diana wanted to format every other row of her 1,000+ row worksheet with a variety of formats to enhance readability.

To change formatting of the spreadsheet:

  • Format line(s) as needed
  • Highlight formatted line(s)
  • Left click Format Painter (paintbrush)
  • Hold ‘Shift’ key
  • Tap ‘End’ key
  • Tap down arrow key
  • Release ‘Shift’ key
    And voila! The formatting was automatically copied to every line in the spreadsheet.
    If you have inserted new rows in an already formatted worksheet, this is a really quick way to fix any formatting problems.

Conditional Formatting and =MOD()

An alternative method, as mentioned above, is to use conditional formatting. If you use conditional formatting and the =MOD() function it is a quick way to create a general ledger type of effect with rows displayed in alternate colors.

Below are the steps you can use to apply a rule to data. In my example here I am using the MOD function and telling Excel to shade every second row of my P&L so that it is easier to read. I am specifying 2 in the MOD function so that every second row is shaded and I am specifying green as that is the traditional accounting color but obviously you can select any color you want – just make sure that it is light enough that that the data can be seen through it.

Formatting dialog box

Formatting dialog box

1. Select your data

2. Click Conditional Formatting on the Home Ribbon.
3. Select New Rule.
4. Click on Use a Formula to determine which cells to format.
5. In the rule description, type =MOD(row(),2)=0.
6. Click Format.
7. Select a light green fill – this is usually associate with accounting general ledgers.
8. Click OK.
9. Click OK.

It should look something like the image below. It really does improve readability.

general ledger formatingt with =MOD

general ledger formatingt with =MOD

Welcome to my Blog for CPAs


Since we have redesigned our entire website, I thought I would change up my blog a bit and make it a bit different than my  Excel-Diva blog.
This blog is directed primarily to CPAs and other business professionals. It is going to cover practical applications of Excel and other software; however, it will also discuss different accounting and financial issues that may  be of interest to you.     Excel-Diva  will remain up since it already has so many Excel tips and how-to’s  out there and I may continue to add some information out there as well.
I hope you find the blog interesting and useful.



  • “It is rare that i can actually say that I enjoyed a CPE course but I found this one informative and very interesting, relying more on the author’s examples and not just quoting tax code!  [Healthcare Reform: The affordable Care Act Tax Provisions]

    - Thomas

  • “The course on ethics was one of the best correspondence courses I ever used.  It was well organized and the examples used were extremely helpful.”

    - Tommy R.

  • ” I was able to get a head start on my credit losses research through your CPE self study this past weekend.  Excellent quality format – so much better than some of the other resources I’ve been using lately.”  Dec 12, 2018

    - Andrea Ll.