Question

Excel allows one to automate routine tasks by recording/writing macros. Please discuss how to create macros and discuss their relevance/usefulness in the accounting world. Can you provide a chart/graph and explanation?

Answer 1

Answer-:

One of the more powerful, but seldom used functions of Excel is the ability to very easily create automated tasks and custom logic within macros. Macros provide an ideal way to save time on predictable, repetitive tasks as well as standardize document formats – many times without having to write a single line of code.

Macros are incredibly powerful and can do pretty much anything your imagination can conjure. As a (very) short list of functions you can do with a macro:

• Apply style and formatting.
• Manipulate data and text.
• Communicate with data sources (database, text files, etc.).
• Create entirely new documents.
• Any combination, in any order, of any of the above.

Steps to creating a macro-:

We start with your garden variety CSV file. Nothing special here, just a 10×20 set of numbers between 0 and 100 with both a row and column header. Our goal is to produce a well formatted, presentable data sheet which includes summary totals for each row.

As we stated above, a macro is VBA code, but one of the nice things about Excel is you can create/record them with zero coding required – as we will do here.

To create a macro, go to View > Macros > Record Macro.

Assign the macro a name (no spaces) and click OK.

Once this is done, all of your actions are recorded – every cell change, scroll action, window resize, you name it.

There are a couple of places which indicate Excel is record mode. One is by viewing the Macro menu and noting that Stop Recording has replaced the option for Record Macro.

The other is in the bottom right corner. The ‘stop’ icon indicates it is in macro mode and pressing here will stop the recording (likewise, when not in record mode, this icon will be the Record Macro button, which you can use instead of going to the Macros menu).

Now that we are recording our macro, let’s apply our summary calculations. First add the headers.

Next, apply the appropriate formulas (respectively):

=SUM(B2:K2)
=AVERAGE(B2:K2)
=MIN(B2:K2)
=MAX(B2:K2)
=MEDIAN(B2:K2)

Now, highlight all the calculation cells and drag the length of all our data rows to apply the calculations to each row.

Once this is done, each row should display their respective summaries.

Now, we want to get the summary data for the entire sheet, so we apply a few more calculations:

Respectively:

=SUM(L2:L21)
=AVERAGE(B2:K21) *This must be calculated across all data because the average of the row averages does not necessarily equal the average of all the values.
=MIN(N2:N21)
=MAX(O2:O21)
=MEDIAN(B2:K21) *Calculated across all data for the same reason as above.

Now that the calculations are done, we will apply the style and formatting. First apply general number formatting across all the cells by doing a Select All (either Ctrl + A or click the cell between the row and column headers) and select the “Comma Style” icon under the Home menu.

Next, apply some visual formatting to both the row and column headers:

• Bold.
• Centered.
• Background fill color.

And finally, apply some style to the totals.

When all is finished, this is what our data sheet looks like:

Since we are satisfied with the results, stop the recording of the macro.

Congratulations – you have just created an Excel macro.

Thankyou...