1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Error-prevention strategies when using Excel

Discussion in 'The Lounge' started by PP3321, Apr 9, 2018.

  1. PP3321

    PP3321 Member

    Messages:
    361
    Dear Chandoo Community,

    I am sharing some of the strategies that I came up with
    in order to prevent errors when working with Excel

    I would be extremely grateful if you could share yours

    1. Visibility of formula
    I try to check every formula contained in the Excel before submitting using VBA below.

    Code (vb):
    Sub DebugPrint_GetAllFormulas()

    Dim c As Range

    For Each c In ActiveSheet.UsedRange
    If c.HasFormula Then
    Debug.Print c.Formula
    End If
    Next c

    End Sub
     
    2. State of mind (reminding me to focus on accuracy)

    I have this message popping up everytime I open Excel
    Code (vb):
    Private Sub Workbook_Open()
    MsgBox ("Fast is fine, but accuracy is everything.")
    End Sub
    Last edited: Apr 9, 2018
    NARAYANK991 likes this.
  2. PP3321

    PP3321 Member

    Messages:
    361
    3. Error Check Sheet in Template (book.xltx)

    I also have error check sheet in Template File, so every new worksheet will contain this sheet in order to remind me to double-check things
    upload_2018-4-9_7-36-0.png
  3. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,457
    Hi ,

    This is a good topic , and worth discussing.

    However , a good error strategy should start with the design of the workbook itself , since planning a workbook thoroughly ensures that changes are not too many and do not come up too often. Most often , it is changes which lead to formulae being tweaked , and inconsistencies becoming a possibility.

    There are auditing software which may help ; see this one :

    https://www.spreadsheetauditor.com/

    See these articles for some tips :

    https://www.aatcomment.org.uk/5-ways-to-reduce-spreadsheet-risk/

    http://professor-excel.com/avoid-errors-in-excel-6-strategies-to-prevent-mistakes/

    One way to eliminate inconsistent formulae is to use tables as much as possible ; not only will the formulae be consistent , they will also be readable.

    Narayan
    GraH - Guido and PP3321 like this.
  4. PP3321

    PP3321 Member

    Messages:
    361
    @NARAYANK991
    Wow thank you for your reply and these articles!

    I realized that designing & planning are very important.
  5. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,428
    Typically I perform same analysis using multiple methods to test accuracy.

    Pivot Table (DAX), SQL native query, Excel formula etc. If there's any discrepancy I'd perform check on each process.

    At minimum, I always use two separate process to compare results. With automated reports, I build in some indicator that flags me when something is off.
    GraH - Guido and PP3321 like this.
  6. PP3321

    PP3321 Member

    Messages:
    361
    @Chihiro
    Thank you so much for your answer
  7. GraH - Guido

    GraH - Guido Active Member

    Messages:
    521
    Hi,
    I would add a "human" factor as well. Depending on the situation I have used these tactics with success to prevent all kind of errors:
    - Explain your workbook design/plan of design to a colleague who has at least your or a better understanding of Excel.
    - Or collaborate with such a colleague. It is kind of an agile approach.
    - Ask a "normal user", or some-one from the intended audience, to use your solution and ask feedback.
    - Ask domain experts to express their gut feeling about some main/critical/sensitive figures. They often have good intuition about the reality.
    PP3321 likes this.
  8. PP3321

    PP3321 Member

    Messages:
    361

Share This Page