Unformatted text preview: Solving Accounting Principles
Problems Using Excel for Windows
to accompany Accounting Principles
Ninth Edition
Rex A Schildhouse, LCDR, U.S. Navy, Retired, M.B.A.
San Diego Community College District, Miramar Campus, San Diego, CA
Jerry J. Weygandt, PhD, CPA, Arthur Andersen Alumni Professor of Accounting,
University of Wisconsin, Madison, Wisconsin
Donald E. Kieso, PhD, CPA, KPMG Peat Marwick Emeritus Professor of Accountancy,
Northern Illinois University, DeKalb, IL
Terry D. Warfield, PhD, CPA, PricewaterhouseCoopers Research Scholar,
University of Wisconsin, Madison, Wisconsin Chapter 10 INVENTORY
Chapter Outline
Filter
Sort
Master Sort Column
SumIf CountIf
Subtotal
Vlookup Filter
Another excellent Excel tool is the “Filter” function. When a worksheet is selected or when specific
columns are selected and the path Data > Filter > AutoFilter is followed filters will be imposed on the
worksheet by Excel. A filter is a tool to screen and restrict the presentation of data. In the “Filters”
worksheet of the Filter data file on the data disk “Filter” has been established. For this worksheet,
columns A and B have had the “Filter” function imposed on them. Unless restricted, through
“AutoFilter”, Excel will put a filter on every column, why this may be a bad idea is shown in the filter
selections of column B where every individual quantity has its own selection criteria. While this does not
hurt, even if not used or appropriate, only Column A is really relevant or usable.
When filters are imposed on a worksheet, Excel puts an additional button on the top of each column
with a filter. This button has an arrow pointing down on it and is contained in the first row of the column.
By clicking on this arrow, you activate a dropdown menu showing a complete listing of every text and
numerical data entry in the column. In the Filter data file, click on the drop down arrow in column A and
click on “Apples.” All of the items not meeting the criteria of “Apples” in column A are hidden and every
row meeting the criteria is moved to the top of the worksheet. This compresses the data and hides the nonrelevant data. By clicking on the dropdown arrow again and selecting “All”, all of the data is presented in
the order that as held before the filtering action was invoked. Utilize the filter arrow for column B and see
the number of options available due to the large number of unique values contained in the column. The
filter database is automatically created by Excel. As noted earlier, this may be unnecessary.
Filters can be placed on every column in the worksheet and multiple criteria filtering is possible. In a
worksheet containing parts locations by state, county, city, warehouse, row and rack, you could filter
column A to only your part number, then filter column B to the State you wanted, column C to the
county, column D to the city, column E to the warehouse. That could easily and quickly take thousands of
rows down to one or two. With only one or several columns containing active filters Excel will maintain
row integrity of data. That is, Excel moves the entire row when a filtering action is imposed on one
column of the worksheet. The “Multiple Filters” worksheet on the “Filter” data file is an example of this
capability.
Filters are removed in the same manner they are invoked. Follow the path Data > Filter > AutoFilter
and they will all be removed. This is called a “Toggled” function – selecting it while it is active turns it
off, selecting it while it is off turns it on. Section 2, Page 59 Page 60 Solving Accounting Principles Problems Using Excel for Windows Sort
Excel will also allow multiple layer sorts. In a “Sort” function no data is hidden and the order of
presentation has several options. In the Sort data file there is a Sort worksheet to show this function. With
Sort it is necessary select the worksheet, the columns, the rows, or the cells that you would like sorted. A
caution that Excel will usually provide for you is that if selecting less than entire rows, Excel will sort
only the selected cells. This may destroy the integrity of the data if completed. The “Undo” arrows may
recover your data if a “Save” operation has not been initiated.
The most convenient way to utilize “Sort” is with titles in row 1 and row 1 frozen through Window >
Freeze Pane while row two is highlighted. This will cause Excel to freeze row 1 and help row 1 to be
identified as labels. The safest way to utilize the sort function is by selecting all cells within the worksheet
by clicking on the “Select All” button above the row 1 number and to the left of the column A letter. After
the worksheet is selected follow the path Data > Sort and a popup menu will appear. Excel will usually
assume that the first row of a frozen pane display is titles so titles should appear as the selection options.
If your presentation shows column letter identification, simply select the “My List Has  Header Rows”
option on the lower left corner of the dialog box. From this menu select Sort by > Part Nbr (Number)
through the dropdown selection pane, Then by > State through the second, or middle, dropdown pane,
Then by > County through the third, or lowest, dropdown pane. You can select whether you want them
in ascending or descending order by clicking on the buttons to the right of each sort level as you desire.
Clicking on “OK” will result in a sorted worksheet by your selected criteria. Worksheets can be sorted
time and time again in different manners without restrictions. This will change the order of the rows,
which, if important may not be recoverable unless a technique such as “Master Sort Column”, discussed
elsewhere, is used.
If the worksheet is not saved upon exiting, it will be in the same configuration and data arrangement
as the last save operation point. Master Sort Column
The “Master Sort Column” is a technique rather than an Excel function or formula. If you are going to
sort worksheets you may be required to return them to their original order for a function such as auditing.
For example, you have entered 41 inventory items into an Excel titled Master Sort Column (this is done
for you on the Master Sort Column data file) worksheet for data manipulation. In the process of working
with the data you discover what appears to be data entry errors that you want to verify. Once sort has been
invoked on a worksheet it is not easy to restore the input or original order. So try putting a “Master Sort
Column” on the worksheet and possibly additional sort columns as required for other reports, sort
preferences, or users. To do this, insert a column at column A by clicking on the Column A identifier.
Then right click on the highlighted column and select “Insert” from the options. This will insert a new
column and “push” the highlighted column one column to the right. You can also insert a column, once a
column is highlighted, through the path Insert > Columns. Excel will insert a new column and push the
other columns to the right. The only time Excel will not do this is if every column to the limit of the
worksheet has been utilized. Now title that new column “Master Sort” (or anything else you desire) and
insert indexed numbers starting in the first data row. You can utilize the row number, such as “2”, as your
first number to keep a match between worksheet row numbers to master sort numbers as shown in the
Master Sort Column data file. Drag or fill this column through the data entry range. Now, whenever you
need it back into data entry order simply select the worksheet and sort by the Master Sort column, column
A, in ascending order, and it is back in data entry order.
Suppose that the Director of Sales wants the inventory file in Part Nbr (Number) order frequently.
Insert a new Column B pushing the existing column B one to the right, and label the new column “Sales
Sort”, sort the worksheet by Part Nbr (Number). Now, while in sorted presentation, put sequential Chapter 10, Page 61
numbers into column B as you did in column A with the master sort. Now any time the Director of Sales
wants his presentation, select the worksheet and sort by column B in ascending order.
Hint: Remember that as the data within the worksheet changes, the sort may need to be run and the
sort index may need to be refreshed. Any party requesting special presentation of the data can have their
own sort row and these columns need not be printed by excluding them from the print area. This
exclusion capability is discussed in the “Print Area” elsewhere in this book. SumIf
Excel has many excellent tools to assist in mathematical valuations. Using inventory as an example, if the
inventory count sheets are entered into Excel worksheets the “Sum If” (“=SUMIF( )”) function can be
utilized to find the individual items and sum the total number of those parts that appears within the
worksheet. This is not counting the number of times the item or value appears within the Excel
worksheet, that function is accomplished through the “CountIf” function. The command string for this
function is =SUMIF(Reference range, selection criteria, sum range). If the selection criteria is text rather
than a number, it must be enclosed within quotation marks such as “Boxes–10”X10”X5”.”
Note: Numbers such as “123456789” should be handled as text to assure the proper results.
For example, the “SumIf” formula that would locate the number “4” within the rage of B2:B100 and
then sum the values within the range of C2:C100 would be =SUMIF(B2:B100,4,C2:C100). The “SumIf”
function to locate the text string of 123456789 within the same range would be
=SUMIF(B2:B100,“123456789”,C2:C100). In the “Sum If” data file on the data disk there is an
example of the “SumIf” function counting apples, berries, and cakes. The caution with this function is
that “Apple” and “Apples” are two different text strings so data entry discipline is required.
Within the “Sum If” data file is a sample of three “SumIf” formulas contained in cells G2, G3, and
G4. These formulas look to column F to determine what they are each looking for – Apples, Berries, or
Cakes. The range they are looking at is column A, rows 2 through 250. The values these formulas are
summing are the corresponding values in column B, rows 2 through 250. Notice that there is one line
identified as “Apple.” This entry, row 26, is filled and font colored for easy locating. If you sum the
individual quantities and include this line in the grouping of “Apples”, you will find that your result and
Excel’s results do not agree. To Excel, “Apples” and “Apple” are two distinctly different items. This is
verified with other formulas on the page and subtotals for your own verification.
These formulas, as provided, all utilize “Look to” formula capabilities as shown in the text
presentation of the formulas in column H. This capability reduces typing, increases speed, and improves
accuracy. In rows 15 through 22 of the formulas columns the text values have been entered with the
required “Apples” type formatting. Excel is not case sensitive as to “Apples” or “apples” with the “Sum
If” formula.
Hint: When using “Sum If” for formula work where all of the items of a column will be placed and
counted into one or more categories make sure that all values are counted with a technique similar to that
used in cells G5 and G9. This reduces the probably of missed data. CountIf
The “Count If” function of Excel will count the number of occurrences of the specified value. In the
“Count If” worksheet within the “Count If” data file the function is shown in operation. The formula is
“=COUNTIF(range for the count, reference value).” The Count If range can be cells, single a column,
multiple columns, a single row, or multiple rows. This is shown on the “Extended Count If” worksheet of
the “Count If” workbook. Page 62 Solving Accounting Principles Problems Using Excel for Windows Subtotal
“Subtotal” is a tool that requires more show than tell to explain. What does “Subtotal” do? It tells you, for
example, all of the flight time Adam, Ann, and the rest of the pilots have flown, and what airplanes they
flew in as well, and the flight time for the aircraft and the total flight time. “Subtotal” requires that the
worksheet be sorted by the basic order for the subtotaling operation since “Subtotals” only works on the
rows format and has no sorting capability within itself. The “Subtotal” workbook on the data disk was
built for this purpose. With the “Subtotal” data file you will need to first sort the data by “Charged Pilot”,
then by “Aircraft”, then by “Date.” This will structure and sequence the data to be “subtotaled” by Excel.
Hint: If you click and highlight your data area to confine the process and avoid taking in extra rows
and columns, Excel may give you an advisory about header rows when you initiate “Subtotal.”
Usually simply acknowledge the message if you know what caused it. If you simply click on a
cell and then follow the path Data > Subtotal this advisory message will usually not appear, but
you may end up with extra rows in your data matrix.
Tip: Identify the data rows by highlighting them and accept / acknowledge the message.
Highlight the rows from row 1 through and including row 147. (This will cause an advisory message
about header rows that is not a factor in this data structure.) Then follow the path Data > Subtotals and the
“Subtotal” dialog box will pop up. Within this dialog box, set the “At Each Change In” window to
“Charged Pilot” through the drop down menu list accessed by clicking on the drop down arrow to the
right side of the window. Other options are available but the data file must be sorted to handle the
selection sequence – if you sort the data file by “Date” and then subtotal by changes in “Charged Pilot”
the resulting matrix will be useless.
In the “Use Function” window ensure that it reads “Sum” There are other mathematical operations
available and you should check these to see your options but you will be using “Sum” in this example.
In the “Add Subtotals To” window scroll up or down to see and select “Time” from the selection.
Ensure that all other items are clear of checkmarks. Then ensure that “Summary Below Data” is checked.
Since this is the first subtotal process on the worksheet, “Replace Current Subtotals” can be checked or
unchecked. This, while not
important now, will be
important shortly. Subtotal Dialog
Box Selections Before you click “OK”,
what has been done is, you
have sorted (earlier) a data
matrix into the order of
“Charged Pilot”, “Aircraft”,
and “Date.” Now you are
telling Excel to determine the
amount of hours each pilot
has flown for this data period. Click “OK” on the dialog box and watch the worksheet change.
The worksheet has new “scroll” bars (discussed soon) to the left and new rows inserted. These rows
contain the data you asked for in
the first run of the “Subtotal”
New Scroll
dialog box. Each pilot’s total flight
bars
time is now in the “Time” column.
Looking at the screen print of the
New Rows
results you can see that “Adam” Chapter 10, Page 63
flew a total of 2.0 hours and Ann flew 7.4 hours.
Now, add a data management feature to the worksheet. In the “First Subtotal” column enter a “2” in
row 2, in row 3 enter a “3”, in row four enter a “4” and then highlight these three cells and drag and fill
the cells down by grabbing the bottom right corner of the highlighted box when your cursor becomes a
heavy dark cross. You should be dragging down through the bottom of the data matrix, now row 210.
You may use this later.
The next subtotal process will address the next issue. Each aircraft has its own billing rate and
numerous pilots flew several aircraft during the period. So, select the rows from 1 through 210 – the
subtotal process increased the number of rows as it added Adam’s and Ann’s subtotals as well as
everyone else’s. Follow the path Data > Subtotal again. This time in the “At Each Change In” window
select “Change in Aircraft”, ensure that the function is still “Sum”, and the “Add Subtotals To” is “Time.”
This time, you do not want to remove the previous subtotals so ensure at there is no checkmark in the
“Replace Current Subtotals” box. If there is or if there is a faded one there, mouse click it until the box is
clear. Now click “OK” and watch the worksheet change again.
Now put a “2” in row 2 of the “Second Subtotal” column, a “3” in the third row, and a “4” in the
fourth row. Then highlight these three cells and drag and fill the column through the data matrix and
down through row 303. Excel and Subtotal has added numerous rows to the basic file. Use any of the
“Save” procedures to save the workbook.
Now the worksheet has more “scroll” bars to the left and more inserted lines. In this process, use the
scroll bars to the right and scroll through the data. You will see that, where a pilot has flown more than
one aircraft in the period, such as Deanna, Excel has subtotaled her flight time in N72RAS as 4.4 hours,
N78CHS as 1.5 hours, and N81JLS as 0.9 hours. Excel also tells you that Deanna flew a total of 6.8
hours.
Another data management issue, column J is titled “Names.” In the J1 cell, enter the formula
“=IF(D2="",J1,D2)” and drag it down through row 301 of the matrix. If you drag it into row 302 in this
example you will “credit” Zoë with the grand totals. Not harmful, just incorrect. This column will be
discussed shortly. Save the workbook to protect and preserve your work.
Now, those new “scroll” bars to the left. At the top of those bars are some new blocks with numbers
indicating levels of data that can be presented. In this example, level 4 is “Full Detail Shown”, the default
screen. Click on the level 3 box in the data file and you have a good presentation to do billing from.
Detail is gone but subtotals are clean and crisply presented so you do not need to find the subtotal within
the detail. Printing at this point will be what you see – summary down to pilot and aircraft. Make sure you
define the print area as you do not need sort columns and unnecessary data. Click on the level 2 box and
you can verify the total hours flown by each pilot, and you are not looking at the subtotals by aircraft
detail. Click on the level 1 box and you are told that your total billing for this period should be 183.7
hours. A nice cross check. Click back into the level 4 box to get full detail shown again. By clicking on
the minus signs on those new scroll bars data will disappear through their associated rows and the minus
sign will be replaced with a plus sign. The horizontal position or orientation in relation to the level boxes
at the top clue you into which data will be hidden or exposed by your clicks.
Click into the “Time” cells of any of the new rows where data appears and you will see new Excel
formulas within the cells. In cell G3 Excel and the “Subtotal” has inserted the formula
“=SUBTOTAL(9,G2:G2).” The “9” within the formula tells Excel this is a summing formula. The range
is G2 through G2. If you open Excel help and type “Subtotal Worksheet Function” you will see the
numerical drivers for the other subtotal functions – if you are interested.
This worksheet contains a lot of formulas and dynamic worksheets can lead to problems. To address
this minor issue, click into the Subtotal’s worksheet and select all cells by clicking on the “Select All”
button or the keystrokes Ctrl (Control)A. Then copy the worksheet through any of the numerous copy Page 64 Solving Accounting Principles Problems Using Excel for Windows
methods such as Ctrl (Control)C, the copy icon on the tool bar, or through the path Edit > Copy. Now
click into the “Invoicing” worksheet of the Subtotal workbook. Make cell A1 the active cell. Right click
cell A1 cell, select “Paste Special” from the options and select “Values” from the dialog box. Now the
Subtotals worksheet is pasted into the “Invoicing” worksheet without the formulas.
Select row 2 and then follow the path Window > Freeze Pane. If row 1 is already frozen through the
paste process, this is not necessary. This will “freeze” row 1 as a header row. To remove subtotal bars
from the “Invoicing” worksheet follow the path Data > Subtotal again for the popup dialog box. This
time select the “Remove All” option and click “OK.” Excel removes the subtotal features. No values are
lost since “Subtotals” will only remove formulas it placed and the new
“Scroll Bars” it inserted and you removed those formulas with the Paste
Special > Values operation just performed.
Now the reason for that “Name” and those various “Sort” columns.
Select the entire worksheet in any manner you like and follow the path Data
> Sort. Ensure “Header Rows” is identified on the dialog box. Then select
“Sort Column”, (column A of the worksheet) which represents the original
data input sequence. Select “Names” in the “Then By” window. The last
“Then By” window can be left blank. Then click “OK” and watch the
results.
You now have original data entry sequence in rows 2 through 147. In rows 148 through 303 you have
pilot subtotals by aircraft and totals flight times – without formulas. However, notice that the presentation
of data is not real good. The detail of aircraft is in column B and the hours are in column G. To solve this
issue, write the following formula in cell K148
“=IF(C148="",CONCATENATE(D148," ",G148),CONCATENATE(J148," ",C148," ",G148))”
Remember that “” is the null value while “ ” is a space. This formula will look at cell D148 to see if
there is anything there. If there is not the formula will “Concatenate” or make a text string out of the
contents of cells D148 and G148 separated by a space. These cells contain the pilot’s name with the word
“total” inserted by the subtotal operation and the total flight time. If there is anything in cell D148, this
formula will “Concatenate” or make a text string out of the contents of cells J148, C148, and G148 (in
that order) and separate them by a space. These cells contain the pilot’s name, the aircraft identification,
and the flight time for that aircraft by that pilot. Since the formula is relational, it can be dragged down
through row 301. Since it is a formula, copy column K and utilize Paste Special > Values to paste it back
in as values. Now the text strings can be moved or copied and they will retain their value. This gives nice
summary presentation for invoicing and records.
Save the data file in its current format to protect your work to this point.
Now that you have pilot invoicing detail on the pilots, you need activity by aircraft. Click into the
“Subtotals” worksheet and follow the path “Data > Subtotals to the “Subtotal” dialog box. Click on the
“Remove All” button. None of your data is lost since you copied it as “hard values” to the “Invoicing”
worksheet. Select all the cells and sort the “Subtotal” worksheet by “Aircraft” and “Then By” “Tach
Out.” This will sort the data by the aircraft and by the sequence of utilization in the event that the data
entry order was not sequential. Once again populate the “First Subtotal” column’s row two with “2”, row
three with “3”, and row four with “4” replacing any data there and then drag down the column through
row 147 (in this example) to populate the cells.
Select the entire worksheet or all the rows of the data matrix. Then utilize the “Subtotal” function
with “Change In” “Aircraft”, “Use Function” of “Sum”, and “Add Subtotal To” of “Time” and remove all
other checkmarks in this category. Then click on “OK.” Subtotal will again insert new features on the
worksheet. In the “Second Subtotal” column, once again populate row two with “2”, row three with “3”,
and row four with “4” and then drag down the column through row 154 (in this example) to populate the Chapter 10, Page 65
cells, replacing any data existing there. If there is data below the current data matrix from an earlier
process, delete or erase it. Select the entire worksheet and “Copy” the worksheet. Now select the “Aircraft
Activities” worksheet. Utilize Paste Special > Values in cell A1 to place the data and remove the dynamic
or live formulas into the worksheet. Then remove the “Subtotal” function through Data > Subtotals >
Remove All. Now, ensure all cells are selected and resort the worksheet with “Sort By” set to “First
Subtotal” and “Then By” set to “Second Subtotal.” The second “Then By” can be set to “None.”
The result will be a sequential presentation of aircraft utilization by aircraft in rows 2 through 147 and
a nice summary of aircraft utilization in rows 148 through 153. You could place the formula
“=CONCATENATE(C148," ",G148," hours")” in cell J148 (of this example) to make a nice text string
presentation and drag that formula through row 153 if desired. If you do, consider Copy > Paste Special >
Values to remove the formulas.
Save your data again to protect your efforts. You can return to the “Subtotal” worksheet and removal
all the “Subtotal” functions and resort by the “Sort Column” which will return your data matrix to input
order. No data will be lost since the aircraft activity data is now on the “Aircraft Activities” worksheet as
“hard values.” Vlookup
Vlookup is another powerful presentation tool of Excel that takes more show than tell to explain. There is
a “Vlookup” data file for this explanation. The “Vlookup” formula is located under the “Lookup and
Reference” category. In cell G5 of the “Vlookup” worksheet the formula looks like this:
=VLOOKUP($F5,$A$5:$E$6,2,FALSE)
The “decode” of this formula is that cell F2 contains the “Seeking value” that Excel and “Vlookup” is
seeking in the data matrix. The range $A$5:$E$6 defines where Excel and “Vlookup” are seeking a
match. Excel will only look DOWN the first column, the A column in this case, to find the match even
though the matrix extends to column E.
Hint: Excel does not put absolute references into the formula so these were inserted so the formula
could be dragged down the “Vlookup” worksheet. The absolute reference on the “seeking value”, the $F5
was manually absolute referenced by column so the formula could be copied right into then next two
columns.
The “2” is telling Excel to return the value found in the second column of the data matrix on the same
line if it finds a match to the “seeking value”, the value contained in cell F5 in this example. This is not
column B of the worksheet except by coincidence. Excel counts columns from the left most column of the
data matrix towards the right. This value will not change as the formula is copied or dragged from cell to
cell since it is not “relational.” The “False” tells Excel and “Vlookup” that if you do not find an exact
match to the “seeking value”, return nothing other than an “Alert” message. If this field is left blank Excel
and “Vlookup” will return the preceding value to the seeking value not to exceed the seeking value. This
can be dangerous and, as an accountant, do you want formulas returning unknown assumptions? The
“false” declaration will return a #N/A# if no exact match to the “Seeking value” is found in the data
matrix.
On the “Vlookup” worksheet there is are several data matrices so the data matrix identification
changes with the “Vlookup” formula location and purpose. In cells G5 through H14 “Vlookup” is looking
at the matrix defined by “$A$5:$E$6” which allows the part to be identified as a pen or pencil part. In
cells J5 through K14 “Vlookup” is looking at a matrix defined by “$A$9:$E$12” which defines the finish
of the pen or pencil. In cells M5 through N14 “Vlookup” is looking at a matrix defined by
“$A$15:$D$16” which informs the part reader if the part is an upper or lower assembly of the pen or
pencil. In cells P5 through Q14 “Vlookup” is looking at a matrix defined by “$A$19:$E$27” which
defines the actual pen or pencil part. Page 66 Solving Accounting Principles Problems Using Excel for Windows
When the formula is written in cell G5 as “=VLOOKUP($F5,$A$5:$E$6,2,FALSE)” it can be
dragged down the rows through 14 and maintain correct orientation and alignment. If you had not made
data matrix absolute reference, when you dragged the formula from cell G5 down to G14, the data matrix
rows would have indexed accordingly and alignment would have been lost. Now you can copy the
formula in cell G5 to cell H5. If the column reference of F had not been absolute, it would have
incremented to the right by one and that integrity would have been lost since you are not seeking a match
to the value in cell G5. Now you can change column value within the “Vlookup” formula to return from
“2” to “4” and the formula will return the value in the fourth column of the matrix when it finds a match.
Since this matrix contains merged cells – columns B and C were merged into one column, the next
column over is considered column number 4. When this change is made, the formula can be dragged from
row 5 down through row 14 and it will maintain proper alignment. Now the formula can be written into
cell J5 as “=VLOOKUP($I5,$A$9:$E$12,2,FALSE)” and dragged down and then copied from cell J5
into K5 and edited to seek the fourth column and dragged down through row 14.
Since the formulas are “live” or “dynamic”, you can change any of the values that the “Vlookup”
formula is seeking and you can see the results change almost instantly. Written into the matrix defined by
G2 through I20 is a grouping of “Vlookup” formulas looking at this feeder matrix. Replacing a number in
column F will cause the “Vlookup” formulas in columns G through I to scan the first column and first
column only of the table defined in the formula as A1 through D71 for that number. If the function finds
an exact match, as controlled by the true/false statement, Excel will return the values of the second
column of the table in column G, the value found in the third column in column H, and the value found in
the fourth column in column I.
Tip: Important issue with “Vlookup.” If you are seeking a value that appears in the data matrix more
than once “Vlookup” will complete its search on the first value found. If you are using the “True” in the
formula instead of “False”, make sure your data is sorted in ascending order since “Vlookup” will
complete its search upon finding the first value equal to or exceeding its “seeking value” and return the
(first) the exact match value or (second) the highest value not to exceed the seeking value. Even if an
exact match is lower in the data matrix.
There is an area on the “Vlookup” worksheet that shows how “Vlookup” can be used to assemble a
mailing label by entering one number in cell G37. Reading the formula will tell you where the “seeking
value” is, the data matrix, and verification that you only want exact matches.
Hint: “Vlookup” is structured for data in a vertical format. If your data is in a horizontal format,
utilize “Hlookup” which works in the same manner for data in a horizontal format. Chapter 11 DISPLAY
Chapter Outline
Freeze Panes
Split Panes
Conditional Formatting
Displaying Zero Values
Gridlines On The Screen Hiding Columns And Rows
Hidden
Indent Within A Cell
Truncate
Workspaces Freeze Panes
Freeze panes is actually “freeze rows or columns.” What this process does is keeps a specified set of
rows, columns or both rows and columns available and viewable at the top for rows, at the left for
columns, or at the top left for rows and columns while you move throughout the worksheet. Open the
Freeze Panes data file. In this workbook there are three tabs. On the “Rows” tab, the rows 1 through 3
were “frozen” in view by clicking onto the row 4 identifier then following the path Window > Freeze
Panes. At this point Excel will “freeze” all of the rows above the line highlighted since there were no
columns selected. As you move throughout the workbook you will notice that the first three rows are
always in visible. This is excellent for titles.
On the “Columns” tab, the “B” column was highlighted by clicking on the column identifier. Then
the “Freeze panes” was invoked through the path Window > Freeze panes. Since there were no rows
identified the column or columns left of the highlighted column will remain visible as you move
throughout the worksheet.
On the Rows and Columns tab the cell B5 clicked into and made active. Then the freeze pane was
invoked through the path Window > Freeze panes. Since a cell with both column and row identity was
active at the time the process was invoked, the rows above row 5 and the columns left of the B column are
now “frozen” into place. As you move left, column A will always remain in view. As you move down,
rows 1 through 4 will always remain in view. This provides you with column and row identity. This
capability is excellent for large worksheets where columns and rows are both elements of identity such as
multiple salesmen in rows and multiple sales items in the columns. By freezing the columns, the
salesman’s identity will always be visible, by freezing the rows, the title of the sold items will always be
visible.
While Freeze Pane is invoked on a worksheet CtrlHome will normally take you just below the frozen
rows and just to the right of the frozen columns as the frozen columns and rows are considered title areas
not normally used for data. You can still enter these areas by simply mouse clicking into them. Section 2, Page 67 Page 68 Solving Accounting Principles Problems Using Excel for Windows Split Pane
The “Split Pane” function is invoked on the Demo File upon opening. This function splits the single
worksheet into two or four separately displayed panes. This view is controlled through the path Window
> Split and, if invoked, Windows > Remove Split. To invoke a horizontally split screen presentation,
place your cursor on the row numbers at the left of the display pane and click into it to make it active.
Excel will highlight the entire row. Then follow the path Window > Split from the menu bar. Excel will
split the screen horizontally and provide a scroll bar for each pane. In this manner the data from the
exercise or problem can be read in one pane while accomplishing tasks in the other pane. To demonstrate
a further enhancement of this function, set your upper pane to show rows 9 through 15 of the demo file
while showing rows 39 through 42 in the lower pane. You can recreate the journal entry by clicking into
cell B39 of the lower pane and typing the “=” (Equal sign) (without the quotation marks), then click your
cursor onto cell B9 of the upper pane. When you press and release the “Enter” key, “Tab”, or click into
another cell, the date of “Jan 2” appears in the date cell of the journal entry, saving you time and reducing
the risk of data error through typing. Since the account to be debited is Cash, you can do one of several
things, you can type “Cash” into the debit account title area or you can do another “Look to” formula for
the account title in the general ledger. Examination of the general ledger will show that the “Cash”
account title is contained in cell B77 while the cash account number, used when posting, is in cell J77. So
try the formulas “=B77” and “=J77” in the appropriate cells of the journal entry to preclude typing in
those values. You can use the “=” sign and then simply click on the cell B77 or J77 to complete the
formula if you desire.
This capability of split screens is handy and precludes excessive scrolling and losing your place while
working within the templates. If you place your cursor near to the center of the display and make a cell
the active cell and then invoke split screen, you will end up with four panes to deal with. In this case, the
control of the display areas is interactive between the left & right and upper & lower panes through the
scroll bars. Becoming comfortable with the appearance and proficient with the use of split screens greatly
reduces your time to accomplish the tasks and reduces scrolling.
Once split screens are invoked, you can drag the border identifiers around to change their position and
sizes. Conditional Formatting
Excel will accept several levels of conditional formatting for a cell under the path of Format >
Conditional Format. On the Conditional Formatting data file there is an example in cell A1. If the value is
less then 1 the cell appears normal, if the cell contains a value between one and ten, the formatting is
horrible, if the value is greater than 10 the formatting gets worse. While this was done for effect, if you
were inputting markups for retail and wanted no percentage less than 50% nor more than 150%, you
could format the cell to go red if the percentage is not within this range as cell A3 is formatted to.
By examining the dropdown menu selection choices of the operator, defaulted to “between” you will
see that you have a wide range of operators to work with. You can impose up to three levels of
conditional formatting on a cell by clicking on the Add button at the bottom of the dialog box. If you have
imposed conditional formatting on a cell you can remove one or more levels of formatting by following
the path Format > Conditional Format while the target cell is selected and clicking delete on which level
of formatting you want removed. Displaying Zero Values
The “Zero values” option on the “View” tab controls whether or not a zero (0) will be displayed whether
the zero is entered as a “hard value”, one entered directly by keying in “0” (the numeral zero), or it is a
“soft value” of “0” (the numeral zero) as a result of a formula action. If selected, checkmark in the box,
zeros will be shown, if not selected (no checkmark in the box), zeros will not be shown. An interesting,
and sometimes, confusing event occurs when “Zero values” are not selected for display. The user will see Chapter 11, Page 69
a “0” (the numeral zero) when he expected to eliminate these values from the presentation through his
“Options” setting. This happens when the actual value in the cell is below the level of presentation but not
equal to zero. If data entry or a formula calculation results in a value below the displayed decimal places
Excel will display the “0” or “0.00”(the numeral zero) value – not because it is zero but because it is
smaller than displayable. If the decimal setting is set to 2 significant digits such as 0.00, the value of 0.00
would not result in a presentation if the “Zero values” option is deselected. However, if the value in the
cell were 0.001, the presentation of Excel would be “0” or “0.00” since this is a value not equal to zero
and it is below the displayed value setting. If Excel is set to two decimal places and the actual value is
0.005, then Excel will show 0.01 as it rounds up the value for presentation. However, it will utilize the
real value of 0.005 in any calculations. Gridlines On The Screen
The gridlines seen on the computer presentation screen are controlled through the “View” tab. These
gridlines are the vertical and horizontal lines on the screen that create the cells. These gridlines are those
seen only on the screen presentation of Excel and greatly aid you in maintaining a positional reference
within the rows and columns of Excel, not those on the printout. Gridlines on the printout are controlled
through “Page Setup” dialog box. Below the selection for gridlines is the ability to change the gridline
colors through the “Gridlines colors” dropdown window. This selection will usually remain selected
except when using Excel for a screen presentation. Hiding Columns And Rows
Excel will allow you to hide a column, columns, a row or rows. The
methodology is to highlight the column, columns, row, or rows you
want hidden. (For this operation, these areas must be adjacent and
continuous.) Then follow the path Edit > Hide from the tool bar or
right click the highlighted area and select “Hide” from the popup
menu. Once the cell or cells are hidden you will see that the column
identifiers and row numbers do not appear to follow the A, B, C, 1, 2,
3 sequence. As shown on the “Hiding Columns And Rows” data file you can see that columns B, D, and
F as well as rows such as 5 and 6 are hidden. If you examine the borderlines between the column
identifiers and the row identifiers you will see a slight difference in their presentation if there are hidden
columns or rows there. These cells remain active in all formula and will still be active for functions like
Go to, Find, and Find and Replace. This technique is excellent for extended calculations that are not
desired in the presentation. However, copy and paste commands are active on these cells and caution
needs to be exercised when a cell or cell is hidden.
To unhide a column(s) or row(s) you have several options. Highlight the predecessor through
successor columns or rows that border the hidden columns or rows through a sweep operation and follow
the path Edit > Unhide from the menu bar or right click the highlighted area and select “Unhide” from the
popup menu. There is also “Hide” and “Unhide” options through Format > Column or Format > Row
from the menu bar.
In the “Hiding Columns And Rows” data file there are numerous areas for you to hide and unhide. Hidden
The “Hidden” capability within Excel is controlled through the “Protection” tab of the “Format Cells”
dialog box. The path to this tab is right click on a cell and select “Format Cells” from the popup menu
then the “Protection” tab on the “Format Cells” dialog box or follow the path Format > Cells to the
“Format Cells” dialog box and select the “Protection” tab. The “Protection” data file can be utilized for
this section. Page 70 Solving Accounting Principles Problems Using Excel for Windows
Assume you are giving a presentation and using Excel as a presentation tool. The worksheet is
extensive and has numerous large formulas contained within it. The object of the presentation is to
convey the information, not have the audience to stare in awe of your formula construction while you are
trying to convey your message. So you can make the formula bar and formula input window disappear by
following the path Tools > Options > View and remove the checkmark from “Formula Bar.” You can
right click the menu bars and uncheck all of the unwanted and unneeded menu and tool bars and make
them disappear. You can format your cells to “Hidden” by highlighting an area of cells or the entire
worksheet and right clicking to get the popup menu and select “Format Cells” or follow the path Format
> Cells and then format the cells to “Hidden” by insuring a nonfaded checkmark is in the “Hidden” box.
Now invoking worksheet protection through Tools > Protection > Protect Sheet will minimize the
appearance of formulas as you click on the worksheet. The results of the formula are still shown.
By formatting any unnecessary for presentation intermediate formulas and unwanted text and cells to
a text coloring the same as the background or fill coloring before protecting the worksheet these
unnecessary items will cause less distraction as they will be hidden  white ink on white paper type
printing. To remove this later simply select the entire worksheet and format all text coloring to
“Automatic” or whatever color you choose through the “Format Cells” dialog box.
Tip: You can also use “Full Screen” through the path View > Full Screen. There is an icon to return
to “Normal” view. If you close that icon off your screen for viewing purposes, you still have the main
menu bar at the top of the screen and can follow the path View > Full Screen, a toggle switch, to get back
to your normal default view.
Clue: The Excel scroll bars will tell you the general data area of a worksheet. If you open a worksheet
and the scroll bars fill the slide area, you are generally looking at the total data entry area. If the scroll
bars are little bars in a large slide area, the data area is rather large. Excel remembers where the data is
and allows you to use the scroll bars to get to it. And, even a “ ” (space) inserted 250 rows below the last
data row and 50 columns to the right of the last data column will cause the scroll bars to change and
represent this placement. Indent Within A Cell
To indent within a cell
without using spaces use
the Indent icon on the tool
Increase and Decrease Indent
bar. Each click on the
icon moves the start of
text and numerical data
about
2
spaces.
Remember that by default text is left justified and numerical data is right justified. Asking Excel to indent
numerical data to the right in a default formatted cell may result in an interesting move. The Indent
Within A Cell data file is available for you to check the operation of the Increase and Decrease Indent
buttons. Column A is default justified left because it contains text. Column B is specifically formatted as
right justified even though it contains text. Column D is specifically left justified even though it contains
numerical data while column E is default right justified as numeral data. Click into various cells in these
columns and click on various combinations of the Increase and Decrease Indent buttons and watch the
Excel response.
Hint: Excel will indent a cell that contains a formula looking to another cell or computing a value.
The exercise and problem templates provided on the data disk are set up to have the “Look to”
formula or function such as “=F12” used extensively to save keystrokes and reduce errors. However, how
do you handle journal entries with a “Look to” reference? On the “Indent Within A Cell” data file in rows
2 through 5 of column H is a mini chart of accounts. The task is to purchase $800 in supplies for cash and Chapter 11, Page 71
make the journal entry in cells H8 and H9 with the values in columns M and N. Utilizing the “Look to”
formula of =H4 in cell H8 you get the presentation of “Supplies” as the debit. In cell H9 you enter =H2
for Cash but it is flush with the left side of the cell while as a credit account it should be indented. Click
on the Increase Indent button and it is. Now enter the value of $800 in cell M8 and have N8 look to cell
M8 to ensure that the cells are always the same.
Now, starting at row 14, purchase $1,000 in supplies by paying cash of $250 and the balance on
account. Utilize Increase Indent to get the proper presentation of credit entries and formulas to have the
cash paid subtracted from the total purchase for the value on account.
Many of the cells within the exercises and problems supplied on the data disk have indent functions
imposed on them in their construction for proper presentation. Truncate
Truncate reduces the number of significant digits from one calculation to another. Within the
“Truncate” data file on the data disk, is a math computation that was accomplished to determine the flight
time for each flight by each pilot. This formula took a value such as (Tach In – column E) tachometer
(engine) hours of 553.0 as recorded at the completion of the flight and subtracted the (Tach Out – column
D) beginning tachometer (engine) hours of 551.4 to determine that Calvin should be charged with 1.6
hours for this flight as shown in cell F2. This same formula is also in G2. The formulas in column G was
then copied and with Paste Special > Values turned into hard numbers. When those hard numbers are
looked at, the values don’t make sense. The formula values are 551.4 hours subtracted from 553.0 hours
and the results should be 1.6 hours but the answer as shown in Excel to fourteen significant digits is
“1.60000000000002”. To avoid passing values that appear this way you can use the “Truncate” formula
as written in cell I2, “=TRUNC(G2,1).” This formula will take the value in cell G2 and reduce it to one
significant digit as instructed by the “1” following the cell reference and comma, trimming those extra
zeros and that inappropriate “2” from the number. However, this is once again, a formula so it needs to be
copied and pasted through Paste Special > Values to eliminate this presentation.
To save the steps, you can use an embedded formula such as shown in column J, “Calc Time 2” to do
the job in one process. The embedded formula is “=TRUNC(E2D2,1)” which truncates the results of
subtracting the out time from the in time and reducing or trimming the significant digits to one in a single
step. You now need to copy and “Paste Special > Values” only column J to complete the process of
eliminating formulas for your worksheet.
The balance of the worksheet in columns I, J, and K are available for your practice.
Note: The use of “Precision as displayed” through the path Tools . Options > Calculations addressed
part of this problem but removes nondisplayed values and detail from the workbook. Workspaces
A workspace within Excel is a structure of one or more workbooks within a preformatted presentation.
The example of a workspace, shown below, is the “Workspace Example.xlw” that accompanies this text.
Notice that the workspace extension is “xlw,” this extension is viewable in the default opening dialog box
of Excel. By opening this single “structure” you actually open all five workbooks – Description.xls, Unit
of Measure.xls, Department.xls, Agent.xls, and Workspace example formula.xls contained within the
workspace in a single action. Since these files are closely related, this is very convenient. The workspace
example formula contains a series of Vlookup formulas, covered in detail in the vlookup section, that
operate in relation to the “A” column of the Workspace example formula workbook. This series of
formulas looks at the other four workbooks to get information. Since they are all related, seeing them at
one time is handy. And by opening the workspace – a special structure within Excel, you have access to
all five workbooks. This workspace looks like this: Page 72 Solving Accounting Principles Problems Using Excel for Windows A workspace is created by sizing and positioning all of the applicable workbooks and worksheets in
the manner you desire. In this presentation, the Description workbook was reduced in size and positioned
in the upper left corner of the presentation by dragging it there with the mouse and changing its size by
dragging its borders. Then the Units of Measure workbook was reduced in size to match the height of the
Description workbook, a similar width, and then positioned next to the Description workbook through a
dragging operation. After that the Department and Agent workbooks were reduced and dragged into place
in a similar manner. Lastly, the Workspace example formula workbook was sized to fill in the rest of the
space on the presentation window. Once all were positioned, the path File > Save Workspace was
followed. The dialog box is very similar to the Save and Save As dialog boxes where you can change the
save location and provide a name for the workspace. Under “Save as type” the only option is “xlw” since
this is a special purpose extension for Excel.
Once saved, the five individual files can still be opened individually through the normal opening
procedures of Excel and can be updated or modified as appropriate and then saved. Their presence within
a workspace does not preclude normal access and operation of the workbook. However, when all
workbooks – i.e. the workspace is required, following the normal opening paths such as File > Open, the
key strokes CtrlO, or clicking on the Open File icon on the tool bar will present you with the Open dialog
box and the workspace name will be presented there. Doubleclicking on the workspace name will open
the workspace and all the contained files. While working in the workspace, you can maximize or
minimize individual workbooks by clicking on the maximize or minimize buttons of that workbook.
The active workbook is controlled by you clicking into the workbook you desire. If you desire to
make an entry into the Description workbook, click anywhere on the Description workbook and the
banner or title bar will become bright while the others will be faded. This indicates that the Description
workbook is the active workbook. You can now click into the worksheet and maneuver around in the
normal manner of movement. As stated, if the size is too small or it is simply easier to work with a larger Chapter 11, Page 73
workbook, use the maximize button in the upper right corner of that workbook to increase its size. while
the workbook is active within the workspace, all of the menu and taskbar commands are applicable only
to that workbook allowing you to save or format the workbook as required or appropriate.
It is important to remember that a workspace is really a structure that contains other workbooks.
There are not restrictions placed on the individual workbooks because they are part of a workspace. Upon
closing a workspace Excel will normally ask you if you want to save any of the contained workbooks that
you have changed since the last save or opening action. Chapter 12 ANSWERS
Chapter Outline
Formula Auditing
Formatting Cells
Merge Cells
Formatting Within A Cell
Goal Seek Null Value
Or
And
If Statement Formula Auditing
Excel has a very useful aid for assisting you with
the checks of your formulas and logic. The
“Formula Auditing” function is controlled
through the “Formula Auditing” tool bar or
menu. To display this tool bar, right click on the
menu bars at the top of the Excel screen, from the
popup menu select the “Formula Auditing”
option from the lists. When this tool bar appears
place and hold your cursor over the various icons
and Windows will display the function of the
icon. The icons of primary interest are “Trace
Precedents” and “Trace Dependent.” Click into
cell C3 of the “Formula Auditing” data file on
the data disk to make cell C3 the active cell.
Then click on the icon for “Trace Precedents” and Excel will display arrows from all of the cells that feed
this cell data. This is shown on the screen print. If the source cell is a formula, Excel will not “trace
through” to that cell’s source. To be effective, the “Trace Precedents” must be activated from a cell with a
formula that references at least one other cell. To remove the audit arrows you can click the eraser on the
tool bar and all audit arrows will disappear, regardless of where your active cell is. To make only one
cell’s audit arrows disappear, click into that cell and click the icon with the negative signs for the action –
dependent or precedent.
Click into cell A1. While cell A1 is the active cell, click on the “Trace Dependent” icon on the
“Formula Auditing” tool bar. A single arrow will appear from cell A1 to cell C3 showing that the data
flow is from cell A1 to cell C3. To see that cell A3 is also a source cell for cell C3 you can either click
into cell A3 and click onto the “Trace Dependent” icon or click into cell C3 and click on the “Trace
Precedents” icon. Once again, the trace icons with negative signs make individual arrows go away, the
eraser on the tool bar makes them all go way. Try this tool on some of the workbooks and worksheets
provided on the data disk if the logic or flow of data is confusing.
You can also access the “Formula Auditing” tools through the path Tools > Formula Auditing from
the main menu bar. With this path you can select a single event or elect to display the tool bar.
Section 2, Page 74 Chapter 12, Page 75 Formatting Cells
Excel has several ways to format cells and the data within a cell. To format a single cell, click into the cell
to make it the active cell. If the format is intended for a single range of cells, click into the upper left,
upper right, bottom left, or bottom right cell and sweep the range with the left mouse button down,
highlighting and selecting all of the cells in the range. Formatting can be applied to more than one cell or
one range by holding the Ctrl (Control) key down while selecting the sequential cells or ranges.
Formatting can also be applied to a single column by clicking on the column identifier, a single row by
clicking on the row number, or on multiple columns or rows by clicking on the column or row and then
sweeping to right, left, up, or down to highlight all the columns or rows or holding the Ctrl (Control) key
down while selecting non adjacent columns.
Once the cell or cells are selected, right click into the highlighted cell or cells and Excel will present
you with the “Format Cells” popup dialog box with the formatting options available at the time. That
dialog box appears below: Format used throughout most of the Excel
templates:
Number > Currency
Decimals Ø (zero) or 2 as appropriate.
$ English (United States) or None as appropriate
Negative numbers as red and contained within
parentheses. From this dialog box you can select any of the many formatting features available at the moment. If a
feature is grayed out or faded, that feature is not available at the moment. For the Excel templates, the
formatting is generally Currency with either Ø (zero) or 2 (two) decimal places as appropriate.
Occasionally 4 or 5 significant digits is set as required by the exercise or problem. Dollar signs, controlled
through the “Symbol” window, are only shown when needed.
In selecting numerical formatting Excel positions the right hand number appropriate for the
formatting selection as to how negative values are presented. Provided below is a display of numerous
Excel formats in positive and negative numbers. A close examination of this screen print will reveal that
not all of the numbers are aligned in the same vertical plane at the right side of the column. This occurs
when Excel is formatted to display negative numbers with a preceding “” negative sign in some cells
while other cells display negative numbers contained within parenthesis “( )”, Excel will position the
numbers farther to the right if a preceding negative sign is used since the space is not needed to the right
in the event of the closing parentheses. The solution for a ragged display of numbers that do not right
align correctly is to check that the formatting of the numbers is all the same by selecting and highlighting Page 76 Solving Accounting Principles Problems Using Excel for Windows
the cells and rightclicking them followed by selecting the desired format even if it appears correct. Excel
will occasionally only show the formatting of the upper left hand cell of a range or multiple selection. The “Format Cell” dialog box also contains the “Alignment” tab. From this tab, you can align the
contents of the cell in numerous horizontal and vertical formats as well as change its orientation and stack
it. Through the “Orientation” formatting function you can rotate your data
through various angles. The insert, shown here, contains an orientation
sample. Another mode shown here is referred to as “Hotel signage”,
referring to the letters HOTEL will be printed vertically on the sign in
front of the establishment. This is also shown in the insert. Text wrap will
try to keep the preset column width while creating a second, third, and
fourth row presentation within the cell of long data.
Tip: You can force line breaks in a cell, as done here, by the keystrokes “AltEnter” within a cell
rather than of simply Enter or a space. In the insert, “AltEnter” produces the same presentation as text
wrap except that you control the break points of “AltEnter” whereas text wrap makes its own decisions.
The functions of “Font” are as expected in any Windows standard application. You can change style,
size, and color through this tab. It is on this tab that you will find “Superscript” and “Subscript” functions
as well as “strike through.” You can also underline the text contained within a cell from this tab including
single and double lines for accounting. However, if underlining is selected, it is only effective on cells
with content in them, including spaces, and is only applicable to the cell. That is, if your entry covers two,
three, or four columns, only the column or columns selected will be underlined and then, only if data
actually appears within them. You can also highlight specific text within the Excel formula window and
underline or format only that text such as shown here: (Addressed in length later.) The “Borders” tab allows you to place borders and intermediary lines to the highlighted cell or cells.
You can put a thick line on the outer edges by selecting the heavier line first and then clicking the
“Outline” icon on the tab. You can then select a thinner line and place lines within the range. Excel will
allow you to place vertical, horizontal, and diagonal lines through a range. The functions available
through this tab are directly associated with the cell or range selected at the moment. Excel will not allow
you to place vertical or horizontal intermediary lines within a single cell through this method, you can
draw a line through the cell as explained in the “Drawing” section of the
book.
Under the “Patterns” tab of the “Format Cells” dialog box you will
find the colors to fill a cell and the available patterns for texture
presentation of those colors. These functions were used on the templates Fill color icon
Font color icon Chapter 12, Page 77
to define the work area, outlined in gray, and the student focus areas, filled in light yellow. To remove a
color, simply select the cell or range of cells, right click the selected cell or range, select format cells,
select “Pattern” and then select “No color.” This will remove both color and texture or pattern. Limited
colors can be added to a cell or range of cells through the “Fill” button or icon on the tool bar. This is the
“Paint bucket” as shown here. All colors can be removed through this button. Fonts can be colored, to
limited number of colors, with the “Font color” button or icon. Next to both the “Fill” and “Font color”
icons is a dropdown arrow to allow color selection. If the bar at the bottom of the icon is the desired color,
simply clicking the icon will place that color in the active cell or cells.
The last tab is the “Protection” tab. This tab allows you to invoke a minor level of protection on the
worksheet or workbook. If you did not want another party to have the privilege of modifying your
worksheet you can select the entire sheet by using the keystrokes CtrlA or by clicking on the cell above
the row 1 number and to the left of the column A letter, then right click the worksheet and select “Format
Cells.” After selecting the “Protection” tab, select the “Locked” option. Ensure that the appearing
checkmark is clear and distinct. A faded checkmark indicates that the selected area contains more than
one level of protection – some protected, some not protected. Also on this tab is the “Hidden” option. If
“Hidden” is selected and activated any functions or formulas will be hidden from the viewer – that is,
even if the cell contains a formula, the viewer will not see it in the formula window. Neither of these
options are active until empowered by following the path Tools > Protection. From this point you can
select “Protect Workbook” or “Protect Worksheet” as well as a couple of other options. When “Protect
Worksheet” or “Protect Workbook” is selected you will be presented with a popup dialog box asking
what privileges you want to grant the users and asking if a password will be utilized. You can leave the
password blank if you desire and it will still be protected or hidden. If a password is provided and later
forgotten, you may not be able to recover the workbook or worksheet. As a matter of technique, if
distributing a password protected workbook or worksheet, you may want to preserve an unprotected copy
in a secure location. None of these protection levels is sophisticated and they can be broken by an
individual with intent, therefore, these levels of protection should not be relied upon to protect sensitive
data. Merge Cells
The “Merge Cells” command is also on the Alignment” tab. This function will create one cell that
occupies two or more rows or two or more columns or, as one group, multiple rows and columns. This
function is used in many of the templates to present instructions and text data in one, large, cell. For
example, the “Name” area is one cell while occupying the range D2 through F2. To utilize the “Merge
cells” function, click into the cell that you want to be the upper left most location of the merged cell.
Then, with the left mouse button held down, sweep right, down, or down and right highlighting and
selecting the cells you want to be replaced by the new, larger, merged cell. When the area is highlighted,
right click the highlighted area, select “Format Cells”, select the “Alignment” tab, and select “Merge
Cells.” The highlighted cells now become one single cell when the “OK” is clicked. Invoking the merge
cell function will result the loss of all data but the data in the upper left most cell of the selection. Excel
will usually advise you that this is going to happen but it is poor data management to rely on Excel’s
protective features for this. If this function is invoked, it can be revoked by selecting the affected cell and
then removing the check mark from the “Merge Cell” option. Selecting “Wrap Text” and / or “Shrink To
Fit” are text functions for the merged cells. The “Wrap Text”, discussed elsewhere in this book, it will
allow multiple lines of text for a single line of text entry. The “Shrink To Fit” selection will automatically
change the size or “points” of font to present all of the text into the cell. Page 78 Solving Accounting Principles Problems Using Excel for Windows Formatting Within A Cell
You can also invoke many of the formatting capabilities for
cells onto the specific elements contained within a cell. For
example, you want to enter into an Excel cell that the interest on
the note payable is due on the 30th of each month. When the
text is entered as “Interest on note payable is due on the 30th of
each month” the text is not automatically superscripted. For this
presentation, select the cell containing the text, then, inside the
formula window, highlight with the mouse the two characters
“th” within the text, then right click the highlighted selection
and select “Format Cells”, then select “Superscript” on the
“Font” tab. Notice that not all of the tabs that appeared when
“Format Cells” was selected while entire cells were selected are
visible now that you have only a portion of the text selected.
Since the additional capabilities are not available, they were not
presented, even grayed out or faded. Many of the formatting
features can still be invoke upon the text within a cell. Formatting Within A Cell Goal Seek
Goal Seek is a tools within Excel that will provide you the question if
you know the answer. With Goal Seek, click into a cell containing a
formula then follow the path Tools > Goal Seek and respond to the
dialog box. The worksheet in the screen print is in the Goal Seek data
file. The path is Tools > Goal Seek. The popup dialog box requires that
you identify a cell with a formula in it, for the example that cell is C4.
Then enter the value you want the formula to end up at, 50 as shown in
the screen print. Then tell Goal Seek what cell you want the value inserted into to create the answer of 50.
The cell identified as “By Changing Cell” must be referenced in the formula contained in cell C4. For the
example cell C2 was chosen. When OK is clicked with the mouse or the enter key is struck, Excel will
produce a popup text box stating that it found a solution to get the value of 20 and that that value is 47
and it asks if this answer is acceptable. Since it is, click on the OK and Excel will modify the value in cell
C2 to 47 and close the dialog box. If the answer is unacceptable hit Cancel and the value in cell C2 will
be left at 1. If Excel cannot compute the value it will error or fault out and tell you it cannot find a
solution. This is usually because cell identified in the “By Changing Cell” window is not referenced
within the formula contained within the “Set Cell” cell. Null Value
The “Null Value” is a special term – it is not a space as imposed by pressing and releasing the space bar,
it is not a letter or character such as a, b, or c, it is not a number such as 1, 2, or 3, nor is it a punctuation
mark or symbol such as !, ?, @, or #. It is the double quote symbol followed immediately by the double
quote symbol such as “” without anything separating them. The best way to define the “Null Value” is it
is nothing and it is not anything else. It is used frequently within formulas to return a “clean and clear”
presentation. On the “Null Value” data file the use of the Null value is demonstrated. The worksheet is
defaulted to show “0” values through the path Tools > Options > View and then ensuring that there is a
full color (not faded or blank) checkmark is within the “Zeros Values” box near the bottom of the screen.
This is also the default setting for most of the exercise and problem templates. This option or selection
will provide you with the presentation of: Chapter 12, Page 79
Sales:
Less: Cost of goods sold:
Gross profit:
Vice:
Sales:
Less: Cost of goods sold:
Gross profit: $100
$100
$0
$100
$100 In the first example it is clear that the value being presented is “0”, it the second example it is not
clear if the formula has even been entered.
The null value is frequently used in if statements as a response. In the “Null Value” data file, the
worksheet determines and presents appropriate periods for depreciation. In cell B4 you enter the life of
the asset in years. In cell B5 you enter the periodicity of depreciation – annual, semiannual, quarterly, or
monthly. The formulas in columns B and E starting on row 12 to determine if the period is applicable and
enter a period number or return the null value, as instructed through cells B4 and B5. Based on the
availability of depreciation periods, the concatenate formula in columns C returns a text string or the null
value while column F returns choice of text strings. As you scroll down the columns to see the difference,
where the periods are not available in the null value column, the columns are blank and appear to be
empty. In the “zero value” column, the response of nonavailable periods is “0”, an unnecessary and
cluttered display and an unnecessary text string.
Assume that the “ ” (space) is utilized instead of the null value or a 0. Almost the same thing will
happen. The appearance will be clean and crisp as shown in columns H and I. However, now Copy >
Paste Special > Values all three examples and you will find the difference. Inside the rows that are not
relevant to the “Null Value” columns, there is nothing – the value the if statements were returning was “”
– Null – nothing. Inside the rows of the Zero Value columns there are a number of zeros since the if
statements were returning zeros. Inside the space columns are a lot of cells with a bunch of spaces in
them. Each unnecessary zero and space adding size to your file.
Tip: These issues affect default page size in printing. Or
The “Or” function of Excel will return a “True” response if any one or more of up to thirty conditions
specified are true. This is shown on the “Or” workbook on the data disk. This data file is a copy of the
“And” workbook with the “Or” function rather than the “And” function. Some of the results are the same,
some are different. For example, now that the formula in cell D1 is saying if any one or more of the cells
A1 through A5 are greater than “2”, return “True.” And, since cells A3 through A5 are all greater than
“2”, the “Or” statement is returning the “True” value.
However, cell G14 now has a minor problem in its resulting logic. Since any one of the conditions is
true, that is, the number of apples equals or exceeds the minimum inventory level, the statement is
returning a “True” to the “If” statement and the “Concatenate” function is telling you to go skiing when in
reality you must order berries. And
The “And” function is found under the Logical” category of Excel’s formulas and functions. It can
compare up to 30 values against your specified requirements and return a “True”, “False”, numerical, or
text string response depending on your requirements. In the “And” data file on the data disk you will find
several examples of this formula in operation. In cell D1 the “And” function is being instructed to check
cells A1 through A5 to ensure that they are all greater than 2. Since only cells A4 and A5 are greater than Page 80 Solving Accounting Principles Problems Using Excel for Windows
2 (cell A3 is equal to 2), the “And” function returns a “False.” In cell D2 the “And” function is being
asked if A1 is equal to 1, if A2 is equal to 2, if A3 is equal to 3, if A4 is equal to 4, and if A5 is equal to 5.
Since they are, as supplied on the data disk, the function returns a “True.” Since the “And” function by
itself can only return a “True” or a “False” in cell D3 the “And” function is embedded within an “If”
statement to return a text string – if all of the cells A1 through A5 are greater than 3, return a “Good”, if
they are not all greater than 3, return a “Bad.” The “And” function is providing the “If” statement a
“True” or “False” determination. And, since the “If” statement is limited to seven layers of parenthesis
while the “And” statement can evaluate thirty values, you have greater latitude in your evaluations.
In cells G10 through G12 the “And” function is being utilized with the “If” statement and the
“Concatenate” function to make a determination and return a clean cell or a text string and a value. (This
is being done for demonstration purposes as an “If” statement and “Concatenate” function can accomplish
this as shown in cells G20 through G22.) The “And” function determines if it is a true statement that
counted inventory is equal to or greater than the minimum level located by the “SumIf” formula. If is, the
return is the “Null value”, if not, it utilizes the “Concatenate” function to state what has to be ordered and
the amount that must be ordered.
The “And” function does not have to see if all numbers or values are greater than a specific value or
less than a specific value. You can mix them up as needed. Inside cell D5 the following formula is placed:
“=AND(A1=1,A2<>3,A3>=3,A4<=4,A5<>A1).” This formula asks if cell A1 is equal to 1, if cell A2 is
not equal to 3, if cell A3 is equal to or greater than 3, if cell A4 is less than or equal to 4, if cell A5 is not
equal to cell A1. If ALL terms are true, the “And” function will return a “True”, if any one or more are
false, the “And” function will return a “False” response. The “And” function will also compare text
strings as required.
Tip: Make sure the question you are asking will provide the answer you need. If Statement
As shown throughout this text and as utilized extensively throughout the exercise and problem templates,
the “If” statement is a very powerful and easy to use logic tool. The “If” statement works on the principle
of comparing term one with term two and if the terms make the condition true, it replies with the first
field after the comparison, if the comparison is false, it replies with the second. The first and/or second
field can be another “If” statement value, formula, or text. The formula format is “=IF(2=2,4,0).” In this
simple “If” statement if 2 equals 2 the formula will put 4 in the cell, if 2 does not equal 2 the formula will
put 0 in the cell. Since 2 does equal 2 the response will be 4. This is shown in cell A1 of the “If
Statement” data file. However, the statement “=IF(2=Two,4,0)” will result in an error presentation since
text strings such as “Two” inside formulas must be contained within quotation marks such as
“=IF(2=Two”,4,0).” This is shown in cell A2 of the “If Statement” data file. If the statement is entered
into Excel as “=IF(2=“Two”,4,0)”, as it is on the “If Statement” data file in cell A3, the response will be 0
as the number “2” is not equal to a text string of the letters “T”, “w”, and “o”. As stated earlier, Excel
does not read English, but it can compare text strings through spell check, F7, which is in reality, a big
“If” statement – if the text string being evaluated matches text strings found in the reference file, look at
the next text string, if the text string is not in the reference file, bring up the dialog box and ask for help.
Excel will compare text strings and provide responses as instructed. For example, the “If” statement
“=IF(“Two”=“Two”,4,0)” will return the value 4, as shown in cell A4, not because it is the sum of Two +
Two, but because the two text strings are equal, as asked, so the first field or the “True” term following a
true statement, is returned.
Tip: There is a detail of Excel that needs to be appreciated in all of the comparative statements. If the
value of 123.451 is entered into a cell with formatting to display two decimal places the entered value will
display as 123.45 but Excel will remember and respond as if it is 123.451. If the value 123.451 displayed
as 123.45 is compared to a displayed value of 123.45 which is in a cell formatted to display two decimal Chapter 12, Page 81
places but that value is actually 123.452, the values appear to be equal, as presented in the formatted cells,
but they are not. Examine the data in the range of E1 through F2 on the “If Statement” data file.
When Excel compares values and the formatting of numerical information is different, for example
“Accounting” with 2 decimals and “Currency” or “General” to 4 decimal places, Excel will ignore the
formatting and compare the values. However, it is possible to have what appears to be a numerical value
such as the 25 contained in cell J1 not be equal to the 25 in J2 since cell J1 as formatted to text before the
25 was entered and, since cell J2 is formatted to General by default, that 25 is numerical and text 25s are
not equal to numerical 25s.
Hint: Try to think of questions that are answered True/False or Yes/No with “If” statements.
The following is a table containing most of the comparison operators available to the “If” statement:
= (equal sign) if the first comparative term is equal to the second comparative term, the first field or
the true field will be returned, if the comparison is false, the second field or the false field will be
returned. Your question is “Is it true that the first comparative term is equal to the second comparative
term?”
< > (not equal to sign) (The order of the operators is important!) If the first comparative term is NOT
equal to the second comparative term, the first field or the true field will be returned, if the comparison is
false, the second field or the false field will be returned. Your question is “Is it true that the first
comparative term is NOT equal to the second comparative term?”
> (greater than sign) If the first comparative term is greater than the second comparative term, the
first field or the true field will be returned, if the comparison is false, the second field or the false field
will be returned. Your question is “Is it true that the first comparative term is greater than the second
comparative term?”
< (less than sign) If the first comparative term is less than the second comparative term, the first field
or the true field will be returned, if the comparison is false, the second field or the false field will be
returned. Your question is “Is it true that the first comparative term is less than the second comparative
term?”
> = (greater than or equal to sign) (The order of the operators is important!) If the first comparative
term is greater than or equal to the second comparative term, the first field or the true field will be
returned, if the comparison is false, the second field or the false field will be returned. Your question is
“Is it true that the first comparative term is greater than or equal to the second comparative term?”
<= (less than or equal to sign) (The order of the operators is important!) If the first comparative term
is less than or equal to the second comparative term, the first field or the true field will be returned, if the
comparison is false, the second field or the false field will be returned. Your question is “Is it true that the
first comparative term is less than or equal to the second comparative term?”
“If” statements can be imbedded in other formulas and can be embedded within themselves. The
maximum is seven layers deep. However, with the ability of Excel to run an “If” statement on the results
of an “If” statement, the seven layer depth restriction is of little concern. If you examine the formulas in
the range of D21 through M26, the “If” statements are embedded upon each other to respond to the input
of cell D22. If the answer is not contained within E22’s string of “If” statements, then E22 looks to E23
for an answer. If E23 does not know the answer, E23 looks to cell E24 for the answer. And it continues
down into cell E26. If all of the possibilities are exhausted, E26’s last option is to respond with the phrase
"I asked you to pick a letter, not a number or a special character. Please try again.", which is the last false
condition respond for the chain.
Hint: Read the section on the “And” function as an additional capability for the “If” statement. ...
View
Full
Document
This note was uploaded on 09/19/2010 for the course ACCT 220 taught by Professor Ullmann during the Fall '10 term at University of Nebraska Kearney.
 Fall '10
 Ullmann
 Accounting

Click to edit the document details