124 PCWorld MARCH 2020HERE’S HOW HOW TO CREATE EXCEL MACROS
pcworld.com/pivt).- Normally, the Product Description
resides in column B, the Quantity Sold in
column C, Product Price in column D,
Extended Cost in E, Discounts in F, Sales Tax
in G, and Totals in H. The column totals are
across the top on row 2, remember? Format
the column widths based on the length of
the field names, and adjust the row height to
20 on all rows. Change the Top/Bottom
alignment to Center, select the justification
you prefer (left, right, center), and then
format the spreadsheet “styles” to your
preference. - Once the master database is set up,
do not move anything. If you need to add
fields, use the Insert Column command. For
example, if you wanted to
add a second sales tax,
position your cursor
anywhere on column H
(Totals) and click the tab:
Home > Insert > Insert
Sheet Columns. The new
column drops in to
become the new H
column, and the Totals
column moves over to I.
This process does not
affect the macro. - The same process
applies to rows. Normally I
would caution you to insert
rows “inside” the active
database area. For example, if the formula
says =SUM(B3:B20) and you insert or use a
row outside of the formula’s range like B21,
the new record’s data is not included in the
formula and therefore, does not calculate.- Now we’ll set up that formula range.
Enter the following formulas on row 2 (this is
a one-time task):
C2: =SUM(C 4 :C 500 )
E2: =SUM(E 4 :E 500 )
F2: =SUM(F 4 :F 500 )
G2: =SUM(G 4 :G 500 )
H2: =SUM(H 4 :H 500 )
Next, enter the following formulas in
these columns (also a one-time event):
E4: =SUM(C 4 *D 4 ), then copy from E4
down to E5:E500
Enter the formulas to calculate the columns and rows.