{"id":2720,"date":"2026-03-23T17:06:36","date_gmt":"2026-03-23T09:06:36","guid":{"rendered":"https:\/\/ehluar.com\/main\/?p=2720"},"modified":"2026-04-01T12:08:58","modified_gmt":"2026-04-01T04:08:58","slug":"automating-audit-procedures-using-the-excel-audit-toolbar","status":"publish","type":"post","link":"http:\/\/ehluar.com\/main\/2026\/03\/23\/automating-audit-procedures-using-the-excel-audit-toolbar\/","title":{"rendered":"Automating Audit Procedures Using the Excel Audit Toolbar"},"content":{"rendered":"
\n
\n
\n
\n
\n

In modern audit engagements, Microsoft Excel remains the predominant platform for executing substantive procedures, analytical reviews, and sampling. Recognising the need for consistency, efficiency, and reproducibility, our firm has evaluated and adopted a specialised Excel-integrated application designed to automate repetitive tasks, standardise outputs, and enhance audit quality.<\/p>\n

Below outlines the key functionalities of the software embedes in Microsoft Excel, categorised by workflow, and serves as a reference for in-house audit teams to leverage automation within existing Excel environments.<\/p>\n

1. Data Cleaning and Structuring<\/h4>\n

The initial stage of most audit procedures involves transforming raw client data into a structured format suitable for analysis.<\/p>\n

UniCleaner<\/strong>
\nThis automated function detects and cleans general ledger (GL) data exported from a wide range of accounting systems. Upon execution, the tool:<\/p>\n

    \n
  • \n

    Identifies the source system (e.g., Xero) using a proprietary detection engine supporting approximately 240 system variations.<\/p>\n<\/li>\n

  • \n

    Generates two outputs:<\/p>\n

      \n
    • \n

      A cleaned, structured dataset formatted for pivot tables, filters, and direct import into audit software.<\/p>\n<\/li>\n

    • \n

      A summarised pivot table displaying opening balances, movements, and a recalculated trial balance.<\/p>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n

      For non-standard or bespoke GL formats, the Automated GL Recognition<\/strong> feature allows users to provide examples, enabling the tool to learn and replicate the cleaning process consistently.<\/p>\n

      Automated Cleaning Functions<\/strong>
      \nA suite of additional utilities supports data preparation, including:<\/p>\n

        \n
      • \n

        Removal of blank rows and columns.<\/p>\n<\/li>\n

      • \n

        Auto-fill of descriptions.<\/p>\n<\/li>\n

      • \n

        Consolidation of multiple workbooks or sheets.<\/p>\n<\/li>\n

      • \n

        Splitting columns or sheets.<\/p>\n<\/li>\n

      • \n

        Remove Ins and Outs<\/strong>: Identifies and isolates accruals and reversals based on reference numbers and values, preserving audit trail integrity while excluding such items from samples.<\/p>\n<\/li>\n<\/ul>\n

        2. Audit Analytics: Comparative Testing<\/h4>\n

        The MatchUp<\/strong> function provides a structured method for comparing two datasets\u2014an essential requirement for opening balance testing, analytical review, and completeness testing across transaction cycles.<\/p>\n

        Users specify an identifier (e.g., account number) and value header (e.g., opening balance) for each dataset. The tool generates a side-by-side comparison with automated variance highlighting, supporting efficient validation of prior-year figures or cross-cycle reconciliations.<\/p>\n

        3. Sampling Methodology and Audit Trail<\/h4>\n

        The Sampling<\/strong> module supports ISA-compliant sampling without prescribing sample size or methodology, leaving those decisions to firm policy and professional judgement.<\/p>\n

        Key features include:<\/p>\n

          \n
        • \n

          Selection of sampling methods (e.g., random, interval, haphazard).<\/p>\n<\/li>\n

        • \n

          Detection of the data range and optional exclusions for material items, low-value transactions, or negative amounts.<\/p>\n<\/li>\n

        • \n

          Automatic generation of a sample sheet<\/strong> containing:<\/p>\n

            \n
          • \n

            Timestamp and user identification.<\/p>\n<\/li>\n

          • \n

            Population size and value.<\/p>\n<\/li>\n

          • \n

            Inclusion and exclusion criteria.<\/p>\n<\/li>\n

          • \n

            Methodology applied.<\/p>\n<\/li>\n<\/ul>\n<\/li>\n

          • \n

            Optional highlighting of selected items within the original population.<\/p>\n<\/li>\n<\/ul>\n

            This design ensures full reproducibility<\/strong>, enabling managers to verify sample selections independently.<\/p>\n

            4. Evidence Verification: ValiData<\/h4>\n

            To streamline the verification of sample items against source documentation, ValiData<\/strong> automates the tick-back process.<\/p>\n

            The tool:<\/p>\n

              \n
            • \n

              Accepts a folder path containing source documents (PDFs, images).<\/p>\n<\/li>\n

            • \n

              Matches document reference numbers (e.g., invoice numbers) to sample data using optional fuzzy matching.<\/p>\n<\/li>\n

            • \n

              Returns a new worksheet with:<\/p>\n

                \n
              • \n

                Automated tick marks indicating verified data points.<\/p>\n<\/li>\n

              • \n

                Discrepancy flags where values do not match.<\/p>\n<\/li>\n

              • \n

                Embedded source documents linked directly to Excel cells for double-click review.<\/p>\n<\/li>\n

              • \n

                A legend explaining tick mark conventions.<\/p>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n

                This functionality reduces manual document checking and strengthens audit evidence traceability.<\/p>\n

                5. Journal Entry Testing System (J.E.T.S)<\/h4>\n

                The Journal Entry Testing System (J.E.T.S)<\/strong> enables multi-dimensional, risk-weighted testing of journal entries, addressing increased regulatory focus on this area.<\/p>\n

                Users select from a range of test categories, including:<\/p>\n

                  \n
                • \n

                  Transaction amounts<\/strong>: statistical outliers, materiality thresholds, rounded amounts.<\/p>\n<\/li>\n

                • \n

                  Date and time<\/strong>: entries posted on weekends, public holidays, outside working hours, or near year-end.<\/p>\n<\/li>\n

                • \n

                  Description and account<\/strong>: blank descriptions, high-risk keywords, zero-balance or seldom-used accounts.<\/p>\n<\/li>\n

                • \n

                  User<\/strong>: identification of irregular or high-risk user activity.<\/p>\n<\/li>\n<\/ul>\n

                  Each test is assigned a risk weight relative to a base risk level. Upon execution, the tool produces:<\/p>\n

                    \n
                  1. \n

                    A Risk Table<\/strong> ranking journals from highest to lowest risk, with specific flags per entry.<\/p>\n<\/li>\n

                  2. \n

                    A Summary Overview<\/strong> showing counts of flagged transactions by test.<\/p>\n<\/li>\n

                  3. \n

                    A Methodology Sheet<\/strong> documenting all calculations and risk weightings, supporting professional judgement review.<\/p>\n<\/li>\n<\/ol>\n

                    6. Templating and Documentation Consistency<\/h4>\n

                    The Templating & Documenting<\/strong> section allows firm-wide standardisation of working papers directly within Excel.<\/p>\n

                    Templates are centrally stored and managed, enabling:<\/p>\n

                      \n
                    • \n

                      Consistent formatting and structure across engagements.<\/p>\n<\/li>\n

                    • \n

                      Instant population of standard working papers into active workbooks.<\/p>\n<\/li>\n

                    • \n

                      Centralised updates to templates, ensuring all teams work from current versions.<\/p>\n<\/li>\n<\/ul>\n

                      This feature supports integration with outputs from other Audit Toolbar functions, such as wrapping high-risk journal entries in a standard review format.<\/p>\n

                      Conclusion<\/h4>\n

                      The above illustrate how the software can integrates directly into Microsoft Excel without requiring changes to existing audit methodologies or systems. We dedicated an audit team that provides onboarding, training, and support to our audit staff.<\/p>\n

                      The software provides a structured, repeatable approach to automating common Excel-based audit tasks. By embedding directly into the existing Excel environment, it enables teams to improve efficiency, enhance consistency, and maintain robust audit trails without displacing current workflows.\u00a0Firm-wide adoption supports alignment with quality control objectives and allows our staff to focus on higher-value analytical and judgement-based activities.<\/p>\n

                      Source:<\/strong> EHLC, 23 March 2026<\/em><\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

                      In modern audit engagements, Microsoft Excel remains the predominant platform for executing substantive procedures, analytical reviews, and sampling. Recognising the need for consistency, efficiency, and reproducibility, our firm has evaluated and adopted a specialised Excel-integrated application designed to automate repetitive tasks, standardise outputs, and enhance audit quality. Below outlines the key functionalities of the software […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"nf_dc_page":"","_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[13,6],"tags":[],"class_list":["post-2720","post","type-post","status-publish","format-standard","hentry","category-auditing","category-techupdates"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/ehluar.com\/main\/wp-json\/wp\/v2\/posts\/2720","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/ehluar.com\/main\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/ehluar.com\/main\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/ehluar.com\/main\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/ehluar.com\/main\/wp-json\/wp\/v2\/comments?post=2720"}],"version-history":[{"count":1,"href":"http:\/\/ehluar.com\/main\/wp-json\/wp\/v2\/posts\/2720\/revisions"}],"predecessor-version":[{"id":2721,"href":"http:\/\/ehluar.com\/main\/wp-json\/wp\/v2\/posts\/2720\/revisions\/2721"}],"wp:attachment":[{"href":"http:\/\/ehluar.com\/main\/wp-json\/wp\/v2\/media?parent=2720"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/ehluar.com\/main\/wp-json\/wp\/v2\/categories?post=2720"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/ehluar.com\/main\/wp-json\/wp\/v2\/tags?post=2720"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}