cart
Student Login to Courses

Category Archives: Excel

Find & Select Constants

January 16th, 2019 by Patricia McCarthy

Posted in Copying and Moving, Excel, Formatting, Miscellaneous Excel Tips |

Tags: , , ,

I just discovered this little timesaver and thought I would share it with you. Okay, to be honest, my husband told me about it.

Suppose you have an Excel spreadsheet and you have a number of product codes all in one row and need to break them out. You can do text to columns but if the number of product codes in the rows are uneven, it can be time-consuming  to collect them all for analysis. Using Find & Select Constants solves this problem.

Let’s use a simple example. Below is a spreadsheet showing certificate ids that customers have been issued.

If I want to break out those certificate ids to count how many of each were issued, I would first select all the data in Column C and then select Data>Text to Columns. As you can see the data is delimited with Commas.


Click Next and select Comma.

Select Finish.
Text to Columns separates all the certificate ids  into separate columns.

In this example, it would be fairly easy to copy and paste the different certificate numbers into Column C but if you had rows and rows of data it would be very time-consuming especially if the certificate ids  extended over into Column L and M or further. This is where  Find and Select Constants comes in.

Select all of Column D and then click Find and Select.

Excel selects all the cells in Column D that contain data.

All the cells are selected so I can just select Copy and then Paste those certificate ids  into Cell C9.

find and select constants in excel

Excel ignores the blank cells and nicely grouped my selection so they are all contiguous. I can then delete the data in Column D since it now displays in Column C and then move on to the next column of certificates.  Unfortunately you need to copy as Excel does not let you cut multiple range selections.

Hopefully you see some applications for this. I found it quite handy.

What is a Pivot Table?

October 17th, 2018 by Patricia McCarthy

Posted in Excel, Pivot Tables |

I have had a couple of people ask me what a pivot table is so I made a short video that walks you through the basics.
One key bit of information that I left out of the video is that if your underlying data changes, all you have to do is right-click the pivot table and select Refresh.
This will make the pivot table update and display the newest information.

Click on the dog with the movie popcorn to watch the video.

Excel video on pivot tables

 

New Excel Functions Coming!

September 26th, 2018 by Patricia McCarthy

Posted in Excel |

New Functions in Excel

I just read a tweet from David Ringstrom and Joe McDaid about dynamic arrays and new Excel functions that are coming soon.

Truly amazing and a huge time saver for many.  Check out this article which shows how each of the formulas works:

https://techcommunity.microsoft.com/t5/Excel-Blog/Preview-of-Dynamic-Arrays-in-Excel/ba-p/252944

It would seem that it will be a slow roll up, so keep your eyes open when you update.
Below are the complete  set of functions that will be accompanying dynamic arrays.
• FILTER – filters an array of data based on criteria you define.
• UNIQUE – returns a list of unique values from a list or range.
• SORT – sorts an array of values.
• SORTBY – sorts an array based on a corresponding array.
• SEQUENCE – generates a list of sequential numbers, such as 1, 2, 3, 4.
• SINGLE – accepts a range or array and returns a single value using implicit intersection.
• RANDARRAY – returns an array of random numbers between 0 and 1.

Using TextJoin

January 25th, 2018 by Patricia McCarthy

Posted in Excel, Functions, Text Functions |

text join

 

 

 

 

 

TextJoin()

TEXTJOIN is a text function that was introduced in Excel 2016 to join text in cells together.
Initially, you may think that the function is not that worthwhile. After all, we already have the ampersand (& ) as well as the CONCATENATE function to join text together.
However, TEXTJOIN really shines when you want to join multiple cells together as you only have to specify the delimiter once.
The syntax is =TEXTJOIN(delimiter, ignore empty, text…)

textjoin function

 

 

 

 

 

 

 

 

 

Let me give you a simple example using a person’s first, middle and last name and their professional designation.
textjoin function

 

 

If we used CONCATENATE, the equation would be : =CONCATENATE(A2,” “,B2,” “,C2,” “,D2).
If we used  TEXTJOIN it is,  =TEXTJOIN(” “,,A2:D2)

textjoin text function

 

 

 

 

 

 

 

 

 

and this result would display:

Lisa Marie Brown CPA

Clearly, TEXTJOIN is a bit simpler and a bit more elegant if you have a lot of cells that you want to join together and you want to use the same delimiter throughout.
You can use different delimiters if you wish and it is still simpler than CONCATENATE as well. In this example, I just wanted spaces after each of the names but I wanted a comma before the professional designation so I used =TEXTJOIN(” “,,A2,B2,C2,”,”,D2) and Excel displayed  the following result.

Lisa Marie Brown , CPA

So, add TEXTJOIN to your arsenal of text functions if you have Excel 2016.

 

 

Sort Left to Right

July 10th, 2017 by Patricia McCarthy

Posted in Excel, General, Miscellaneous Excel Tips, Worksheet Design |

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.

Using TRUNC()

May 12th, 2017 by Patricia McCarthy

Posted in Excel, Functions, Misc Functions, Miscellaneous Excel Tips |

This is an excerpt from our Excel CPE course – Must Know Excel tips Tricks and Tools for CPAs.

TRUNC()
No Decimals
TRUN - no pennies

If you don’t want the pennies in your calculation you can use =TRUNC(), short for TRUNCATE, or =INT(), short for INTEGER,  to turn your data into integers.

 
The two functions work differently but return the same value when working with positive numbers. You will get different answers from these two functions with negative numbers. TRUNC() removes the fractional part of the number whereas INT() rounds down to the nearest integer based on the value of the fractional part.

In the screen shot below, -6.45 returns a different value using INT as it -7 is the lower value.

TRUNC

 

Understanding Excel Time Value of Money Functions

April 14th, 2017 by Patricia McCarthy

Posted in Excel, Financial Functions, Functions |

Tags: , , , , , , ,

Understanding Excel Time Value of Money Functions

Below is an excerpt from our  Excel Time Value of Money Functions for CPAs course.

There are certain terms and conventions in Excel that are extremely important to understand prior to working with Excel time value of money functions.  The most important to understand are the terms used in Excel.

Most Excel time value of money functions contain four or five basic inputs.  They are:

  1. Pv – present value. Used for both single sums and annuities.
  2. Fv – future value. Used for both single sums and annuities.
  3. Nper – number of periods. Used for both single sums and annuities.
  4. Rate – interest rate for period. Used for both single sums and annuities.
  5. Pmt – periodic payment. Used only for annuities.

Functions may be entered directly or a function wizard may be used for input.  If the functions are entered directly, the required inputs and structure are below.

  1. =Pv(rate, nper, pv,fv, type)
  2. =Fv(rate, nper, pmt, pv, type)
  3. =Nper(rate, pmt, pv, fv, type)
  4. =Rate(nper, pmt, pv, fv, type, guess)
  5. =Pmt(rate,nper,pv,fv,type)

Keep in mExcel time value of money ;ind that, at a minimum, only three of the basic inputs are required to arrive at the correct answer.  Type (not one of the basic inputs) refers to when annuity payments are made (either at the beginning or end of a period) and, if omitted, the default is at the end of a period.  Guess (also not one of the basic inputs) is literally a guess of the correct answer and helps Excel narrow its calculations. It is not required, but may be necessary in the IRR calculation to arrive at an answer.

Most find it easier to use the function wizard as it is not necessary to remember the function inputs and you can see the function result prior to clicking “OK”.  For the Pv function, the function wizard is displayed below.

Excel uses a sign convention that indicates whether an amount is a cash inflow or cash outflow.  It gets a little confusing.  Basically, if you’re investing, that amount would be reflected as a negative amount – a cash outflow to the investment.  If you’re receiving a loan, that would be a positive amount as it is a cash inflow to you.  A cash payment for a loan (Pmt) would have a negative sign while cash received from an investment would have a positive sign.  Some people are confused when they compute a payment or a present or future value and it results in a negative amount.  However, there are certain calculations where the sign convention is very important.  This will also be covered in greater detail in the specific examples contained in the course.

Of the five (or six) function arguments that are available, make sure that if they’re not needed, they are set to blank or zero.  Anything other than blank or zero will enter into the calculation and make the result incorrect.  As an example, if you want to know the present value of a single sum and your inputs are Rate, Nper, and Fv, make sure that Pmt has a value of blank or zero as this argument is not needed.  While Type should have a value of zero as well, it is ignored in a single sum calculation.  However, in an annuity example, you would need to be more aware of the Type argument as it would have an impact on the result.

Excel does not have an option for compounding frequency.  In Excel, Nper must be the total number of periods and Rate must be adjusted so that it conforms to a per period basis.  As an example, if you’re dealing with a 5 year loan at an annual interest rate of 12% where payments are monthly, there are a total of 60 monthly payments in 5 years.  Nper in this case is 60.  Since the payments are monthly, Rate must also be adjusted from an annual rate to a monthly rate by dividing the annual rate by 12.

The Rate argument must be entered as a decimal.  If you want to enter 12%, it must be reflected as .12.  Having 12 as an input in this example would provide an incorrect result.

The “Type” argument is only used in annuity calculations and is used to establish whether the payment is made at the beginning of the period or at the end of a period.  The default in Excel is at the end of a period.  However, there are instances where the cash flow occurs at the beginning of the period and, in these instances, you must change the Type argument to 1.    In an annuity calculation, the resulting answer will be different depending on the Type argument selected.

While not required, it is suggested that cell references be used as inputs for these functions.  By doing this, it is easier to review and should result in a lower likelihood of input errors.  It also makes it easier to change inputs.

RRI Function

February 13th, 2017 by Patricia McCarthy

Posted in Excel, Financial Functions, Functions |

Below is an excerpt from our Excel Time Value of Money course written by Joseph Helstrom,CPA.

RRI

RRI is a new function that was introduced in Excel 2013. It returns the equivalent interest rate for the growth of an investment.  The inputs required are the number of periods, the present value and the future value.
As an example, let’s say you invested $100,000 for 8 years compounded annually and the investment grows to a value of $150,000.  What is the equivalent rate of return?  In these examples, we will type the formula directly into the cell and not use the function wizard.
Start in the cell with =RRI( and then type the inputs as shown below.
RRI function

 

 

 

The answer:

RRI - Time value of money function

About 5.2%.

Remember, compounding periods are important.  What if the investment was for 8 years compounded quarterly?  You would need to adjust NPER for the quarterly compounding so it would be 8 years x 4 compounding periods per year = 32.  You will also need to adjust the result if the desired answer is an annual rate.  You can type a new formula by starting with =RRI( and then typing the new inputs or selecting the cell and pressing F2 to edit the existing formula.
RRI function

 

 

The answer:

RRI -Time Value of Money function

 

 

This is the answer for a quarterly rate.  When multiplied by 4 to translate it into an annual rate, the answer is .012751 x 4 = .051004 or about 5.1%.
The quarterly compounding slightly reduced the rate necessary to achieve the same result.

Dashboard Design

February 8th, 2017 by Patricia McCarthy

Posted in Excel, Miscellaneous Excel Tips, Worksheet Design |

Tags: ,

I just finished updating my Excel Dashboards course, Drive Your Dashboard with Excel,  and thought I would share an excerpt.

Dashboard Design

Creating a dashboard is NOT the same things as creating an Excel spreadsheet. By this, I mean that people tend to open an Excel spreadsheet and just start entering data. They worry about formatting and layout later – if at all.  (No, I am not talking about you.)
This is not true of creating a dashboard. The design of the dashboard is key.  Now, don’t roll your eyes. If you don’t know who your audience is and what the purpose of the dashboard is, it will not be successful. You will have simply wasted your time and probably confused your audience.

 To design a dashboard properly you need to know:

  • Who will be viewing the dashboard?
  • Who will be using the dashboard?
    • Viewers and Users may have different purposes
  • What they want to dashboard for – what measurements?
  • What metrics do you want to track?
  • What do users want to learn from dashboard/ how do they plan to use it?
    • High Level overview or detailed operational needs

Once you understand who your audience is and why you are designing it, you need to consider:

  • Where is the data located?
  • How is the data going to be retrieved?
  • Who will maintain and update the original data?
  • Who is going to update the dashboard’s data once it is created?
  • How should the dashboard be structured?

Design Layout

KISS- Everyone knows the Rule – Keep It Simple….

Dashboards, with their colors and graphics, usually resemble a magazine layout. Appearance is important but don’t get so focused on the graphics and layout that you lose the data.  This is becoming more and more important as dashboards gain in popularity and everyone is trying to make them look so pretty.
Edward Tufte, the acknowledged “founder” of dashboards, discusses basic techniques for improving the display of quantitative information. He has an interesting website which can be found at http://www.edwardtufte.

Below are some basic rules to keep in mind as you design a dashboard:

  • Plan and design a mockup so that you know what you are doing with your space.
  • If there is too much data, it is difficult to see what is really going on so keep it clean and simple.
  • Do not use a lot of different colors.
  • Do not use a lot of different fonts.
  • Format consistently – For example, do not use accounting format in one section and currency format in another section.
  • Select a single background color for charts or make the chart areas transparent.
  • Use custom formats for large numbers and/or abbreviate where possible.
  • Use white space to indicate sections to create the idea of more space (no borders).
  • Only include needed graphics such as charts.
  • Don’t get cute- it is not a PowerPoint presentation.
  • Generally, the top of your dashboard will contain overview information and more specific information should be found underneath it.

Dashboard Structure

dashboard design

Basically, you need to break your dashboard area into sections.
At an absolute minimum, you should have 3 sections in your workbook/files:

1.Data/Input Section

  • The Data section is where the raw data is. If you are importing from other programs, such as Access or QuickBooks, this is where you would want the data to reside

2. Analysis Section

  • The Analysis section contains the formulas, which pull the data from the Input section, and organizes it so that it becomes information. This is the section that will feed the Presentation section.

3.Dashboard (Presentation) Section

  • The Presentation section pulls and/or reshapes the key information from the Analysis section into the actual Dashboard.

Other sections to consider adding include:

  • Control Section
    • Many people recommend having a Control section or sheet that would contain informative that would be used multiple times in a dashboardsuch as listing of dates, listing of products or sales regions. In other words, data that you could reference or link to multiple times.
  • Help Section
    • Some people recommend having a Help section or sheet, which can be very useful, if one or more people are maintaining the dashboard You can also include comments and explanations of calculations there as well. You may think you will remember why you put a formula together a certain way, however, there is a good chance that you may forget what your reasoning was a year later.
  • Table of Contents or Reports Page
    • A Table of Contents can be very useful if you are using a lot of worksheets. You may also want to consider having a Reports Page and then pull the Dashboard from that page.   Make sure your set-up allows for flexibility.

For example, your raw data section may contain Sales by month by sales person by product for last year and this year as well as budget numbers.  In the Analysis section, you may have created a pivot table that summarizes sales by product for the month and quarter. The presentation (dashboard) section may display the quarterly sales by product as compared to budget or last year.

Keeping all the data together in one workbook is obviously the easiest method; however, these sections can all be in separate workbooks depending upon your preference and the volume of data and analysis. This is one of the reasons that preplanning and layout are so important. You don’t want to be in the middle of your creation and suddenly realize that it won’t work or that you forgot an important component that now needs to be incorporated in somehow.

When I reread this section, I realized that most of the information included here was also included in a course I wrote earlier on designing a database.  I cannot emphasize enough that preplanning and design are the most important aspects of a dashboard.  If you don’t know who your audience is and what they want the information for then you have just wasted a lot of your time as well as theirs.

Fixed Decimal Places

December 2nd, 2016 by Patricia McCarthy

Posted in Excel, Formatting |

Tags: , ,

MACRO TIP FOR FIXED DECIMAL

I have a Fixed Decimal Place tip, in my Must Know Excel Tips Tricks and Tools for the CPA Ebook.  Eric Robinson read my tip and sent me his own tip on fixed decimal places.
If you are unfamiliar with the Fixed Decimal feature in Excel, it is pretty cool.
 If you need to enter columns of numbers, with a fixed number of decimals, into a worksheet, you can have Excel enter the decimals for you, using the fixed decimal feature. So, if you set the decimal point to 2 and then type 12345, Excel will display 123.45. It is very easy to use but you do have to turn it on and off so Eric’s macro tip is very handy.
I have a link below if you wish to read more about the feature.
Eric’s macro allows you to switch back and forth between no decimal place and  decimal places. Switching back and forth can be tedious if you are, for example, entering a long column of check numbers (no decimal) and check amounts (2 decimal places).  I have shared his macro instructions below. Thanks so much  Eric.

Sub ToggleAutoDecimal()
‘
‘ ToggleAutoDecimal Macro
‘ Macro recorded by Eric Robinson to handle fixed decimal places
‘
    Application.FixedDecimal = Not (Application.FixedDecimal)

 

End Sub

Here is a link to the instructions on how to use fixed decimals on my Excel-diva blog.

fixed decimal place

X Axis Units in Excel Charts

June 16th, 2016 by Patricia McCarthy

Posted in Charts, Excel, Miscellaneous Excel Tips |

Tags: , , ,

X Axis Units- Excel Charts

I thought I would share this problem I ran into charting so that you don’t start banging your head against the wall as I considered doing.
I have used this same data in a many different versions of Excel and never had a problem until Excel 2016.
I charted some data and I went merrily on my way until at some point I noticed big problems with my X axis.
Do you see the problem? Take a look at the X axis. At first glance, it looks okay and then… hmm.. issues here.

Excel 2016, by default, apparently uses days so it is showing the first day of the month.
Unfortunately, most businesses are more interested in the last day of the month.chart units - dates

To fix this, right-click on the X axis and select Format Axis and change the base from Months to Days.
x Axis - format axis

Your chart should now correctly reflect the data from Column A.

revised chart - x axis

 

 

  • “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 wanted to compliment you on the Accounting Fraud – Recent Case Studies (AUCASEMC) course I just took. The ebook was fantastic! Concise, easy to understand, very well structured, and very interesting. Exactly the way I like it. Thank you for putting it together. I feel it took me enough time to be worth 2 CPEs, but it was fantastic nonetheless. Thank you.

    - Mark R.

  • “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