Alt plus = to quickly sum

I just picked up a quick way to quickly total a large set of numbers in Excel 2007/2010 and I wanted to share it with you.   With this tip you select the data and then press the Alt Key and then the equal sign key.

Below is a simple table of data.
Simply select from A3 to F9 and then press the Alt key and the Equal sign.
Alt+=
This will automatically total Column F and Row 9.

It is pretty flexible - I  get the same result if I select A4 to F9 or B4 to F9.
Have a great weekend.

If  you are looking for more great tips check out my Tools and Tips CPE course for Accountants.
http://www.cpaselfstudy.com/item_115/Excel-Tools-Tips-for-the-Accountant.htm

Page Orientation - 2 Ways to Change It.

The other day, I was sitting there changing the page orientation in Excel to LANDSCAPE AGAIN and thought AGAIN that there has to be a better way. I was also doing a lot of muttering about Microsoft and why the heck they have the default as portrait instead of LANDSCAPE anyway. Seriously, how often do you change your page orientation and think why am I doing this AGAIN?

Anyway.. I digress... of course I am thinking that I should stop being so lazy and complaining and just change it. So, I am sitting here thinking about changing something in Options or creating a macro because even though I use templates and create them from time to time - I totally spaced it and did not consider the obvious solution.  I was discussing this with some members of the Excel BlackBelts group on LinkedIN and of course a number of people immediately pointed out that I should just create a template or edit the existing  template.
(Great group by the way- if you are looking for an intermediate to advanced Excel group).

James Travers, a member of ExcelBlackBelts, gave me such as great example that I knew a lot of you would find it useful. His comment is below:

The default workbook is a template file called book (with the appropriate template extension .xlt, .xltx or .xltm) .
The default worksheet is a template file called sheet (again with the appropriate extension). These are in your xlstart folder which by default is in (on an XP machine) c:\Windows\Application Data\Microsoft\Excel\XLStart or c:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART but you can specify a different one in Excel Options.

Any other workbooks that are in that folder are automatically loaded on Excel start up.
I have my defaults set up with the company standard header & footer, print settings, corporate theme, cell styles, pivot, slicer & table styles. I also have an add-in to apply these settings to any other workbook on 1 click of a custom ribbon command button.

We've rolled the same settings out to the entire company through a log-on script meaning that all workbooks that get created now automatically have the correct format and any legacy workbooks can be updated with 1 click.

If you are interested in the add-in, feel free to contact James Travers, on LinkedIN.

If you can't change your template due to network restrictions etc, another option that was pointed out is to add the Orientation button to the Ribbon. To do this, go to File>Options and select Customize Ribbon. From the Choose Command from drop-down arrow, select All Commands. Scroll down and click on the first Orientation icon. Click Add and specify where on the Ribbon you want it to display. When you go back to Excel you will see the Orientation icon on the ribbon and when you click on it, you have the option of Landscape or Portrait.

Be still my heart..........look how much clicking this is going to save you!

Spreadsheet Controls under Sarbanes-Oxley Section 404

This is based on an EBook Joe Helstrom, CPA wrote for CPASelfstudy.com entitled Spreadsheet Controls Under Sarbanes-Oxley Section 404

Spreadsheet Controls 

Spreadsheets have become pervasive in most companies and have many uses. Those that are used in the financial reporting process are of most concern to the assessment of the effectiveness of internal controls over financial reporting mandated by Sarbanes-Oxley. Several steps are recommended to accomplish the needed assessment related to spreadsheets. The first would be to get a handle on the population of spreadsheets that are used in the company. Secondly, determine whether the spreadsheet is used in the financial reporting process. Next, identify risk factors of the spreadsheet and grade the overall risk. Next, identify any compensating controls that reduce or mitigate the identified risks. Lastly, determine what remediation steps are necessary, if any, for the identified spreadsheets.

 

It should be noted that a high risk spreadsheet used in financial reporting, even with compensating controls, may not be able to achieve an adequate level of control in a spreadsheet environment. It may be necessary to migrate the process to an application where the information technology controls are developed and maintained by the information technology function.

 

Inventory all spreadsheets
The beginning of the “top down” approach would be to identify all spreadsheets used by the organization in the financial reporting process. This would include financial reporting, plant accounting, treasury, tax and operations. This can be done at the department level by asking each department head or supervisor to create a list of all spreadsheets used with the following information:

 

• Spreadsheet name
• Location of the spreadsheet file
• Department using the spreadsheet
• Description of spreadsheet purpose
• Spreadsheet users that have access

 

In addition to the above, the IT staff can be enlisted to query the company’s networks for spreadsheet files. This will help ensure that the inventory is complete.

 

Determine how current spreadsheets are being used

Once the spreadsheet inventory has been completed, an assessment of spreadsheet use must be performed. The first step is to segregate the spreadsheets into categories. These categories may include financial, operational and analytical.

 

The spreadsheets that fall into the “financial” category will carry the most risk potential. These will include spreadsheets that:

 

• Validate account balances

• Support transactions or journal entries

• Compute financial statement disclosures

• Perform financial reporting controls

 

Operational and analytical spreadsheets may also be important depending on the organization. However, these generally are used for operational decisions rather than in the financial reporting process.

 

Determine the risk factors of the spreadsheet

The financial spreadsheets (as well as any others that are significant to the financial reporting process), must be assessed for risk. Risk factors will include:

 

• The use of the spreadsheet and the use of the spreadsheet output

• Materiality of the affected account balance or disclosure

• Potential errors in downloaded data such as an incomplete download or a download of incorrect  data.

• Whether the spreadsheet uses complex calculations, formulas or macros.

• Number of individuals using the spreadsheet

• Size of the spreadsheet

• How well the spreadsheet is documented

  

These risk factors must be assessed along with the use of the spreadsheet and a risk rating should be assigned. As an example, a spreadsheet that is used for financial reporting disclosure, uses downloaded data, contains complex calculations and is used by a number of people would have a high risk rating. A similar spreadsheet used solely for analytical purposes would likely carry a moderate to low risk. Additionally, a spreadsheet that is used for a key financial control would likely carry a higher risk rating than one that is used to provide a list of documents. This is a subjective determination. It must be well documented so that a reviewer can assess the conclusions drawn by the company.

 

Evaluate compensating controls for risk factors

Certain organizations have already put controls in place to reduce the risk of material financial reporting error related to spreadsheets. These controls must be evaluated in light of the risk factors noted above and, once again, a determination must be made as to the effectiveness of the compensating controls. Compensating controls may include:

 

• Downloaded data has control totals that are compared the source data and validated by the user.

• If applicable, control totals or logic controls are used to validate user input.

• A logic inspection of the spreadsheet by an independent party is performed and documented prior to spreadsheet use.

• Spreadsheets are protected against unauthorized changes.

• Spreadsheet versions are used and, before a new version is utilized, it is tested and approved.

• Access to the spreadsheet is limited to authorized users via network access limitations and/or use of spreadsheet passwords.

• Spreadsheet documentation is adequate and up to date.

 

Once again, a determination must be made as to the adequacy of compensating controls. This can be a grade of either “Good”, “Moderate” or “Ineffective”. It is also a subjective determination.

 

Documentation of procedures

The spreadsheet inventory, description of use, risks and compensating controls should be summarized in a spreadsheet or workpaper. The documentation should also include your risk and control grades as well as a testing strategy for those spreadsheets that are deemed to have adequate compensating controls. Keep in mind that once a control has been identified, it still must be tested.

 

Remediation

For those spreadsheets whose compensating controls are moderate to ineffective, there should be changes made to enhance the compensating controls. Excel supports many compensating controls.

 

Keep in mind that a spreadsheet may not be appropriate for high risk accounts. In cases where the risk is high and the balance is material, migration to an application supported by information technology staff and control environment may be warranted.

Creating Multi-Tiered List with Data Validation and INDIRECT

I found a great tip on one of my LinkedIn Excel Discussion groups. It was how to create a multi-tiered list.

The example used was state and city.  Using a data validation in Column A to list states, the person wanted the associated cities to display in Column B.  In other words, if Cell A1 displayed Indiana then B1 should display a list of related cities. If A1 changed to Massachusetts then the list of related cities should change to reflect that.

I thought I would share this tip with you but I wanted to take it a step further so that you can see how to do this using separate sheets and not have all the data in one sheet. I also decided to do a different example so that you would see some other uses for it.

We are going to create a Product List and as different products are selected, the associated model numbers will display.

Let's go through this:

I have 2 sheets - a Summary sheet and then a Model Information sheet.
Here is the sheet named Model Info. It contains the data we are going to work with.

Here is the Summary Sheet:

Put your cursor in cell A2 and then go to the Data tab and select Data Validation.

Select List from the Allow: dropdown
Put your cursor  in the Source: dialog box and select A1:A4 on the Model Info sheet.
Click OK.






The Summary sheet now has a drop-down arrow at A2.

Click in A2 and copy that blank cell with the drop-down arrow down to A5 or A6.
Click on A2 and select the drop-down arrow and select VCR from the list we just created.

What we want to do now is set up Column B so that it will display all the models associated with whatever is displaying in Column A.

Let's go to the Model Info sheet and create a range name for all of that data.
Select A1.I14 on the Model Info sheet. 
Click on the Formulas Tab and select Create from Selection.

Make sure that there is only a checkmark in left column and click OK.
This is an easy way to create multiple range names all at once. We are telling Excel to select the Left Column which in this case are the Product Line names in Column A and create that as the range name for all of the data to the right – the model numbers. This is a lot faster than individually naming each of the ranges.

Now, let’s go back to the Summary sheet for the final steps.
Select all of  Column B (select Column header).
Select Data Validation again.

In the Allow: dialog box select List again
In the Source: dialog box, type =indirect(A1)
(make sure that it is a relative reference - not an absolute cell reference. If it an absolute, every cell will display the same contents).
 
Click OK.
 
 
 
 
Since we are referencing A1 that contains the word Product, Excel is going to give us an error message. When you see it just click Yes that we want to continue.

 
 
 
To get around the error message, you could have selected specific cells in Column B but I took the lazy way of selecting the entire column.
 
 
Click on the drop down arrow at cell B2 and you should see all the model numbers associated with the VCR product line.
Click on A3 and select another product and when you click on cell B3, you will see model numbers that are associated with that product. In my example, I selected CAM for camcorders and B3 shows all the associated model numbers.

Another way to copy!

Everyone has their favorite way to copy. I have lost count on the how many ways there are to copy in Excel - but here is one that many of you may not be aware of. It is a quick way to fill down a column or across a row.

• Select the range first
• Enter the formula or value that you want to replicate
• Press Control and Enter at the same time

Whatever you typed in the first cell will carry down throughout the entire range you selected.  This only copies the formula or value typed - it does not copy any pre-existing  formats that were in the first cell. If you format the data in the first cell and then press Control and Enter, the formatting will also replicate.

A lazy way to get rid of error messages

People spend a lot of time using IF statements and/or  IFERROR so that error messages do not show up in a spreadsheet but if you are in a hurry and need to present your data- here is a lazy way to fix it so that they don't display in a printed report.
Simply go to Page Setup and click on the drop-down beside Cell Errors as: and select

Spreadsheet Design 101

So many people, ignore spreadsheet design but it is important if you are building a complex worksheet or one that you will be referrring back to over time.

Spreadsheet Design


Two things make a good Excel spreadsheet. Good data is obviously one of these things but the other that is often overlooked is good design. Good design helps to ensure a reduction in errors which is obviously key.

A nicely formatted spreadsheet is nice and clearly it is easier to understand; however, a nicely formatted spreadsheet and well designed spreadsheet are not synonymous. A well designed spreadsheet is concerned with the organization of data and assumptions rather than appearance although obviously appearance is a factor – it is just not the primary factor.

Good design helps to ensure:

• Navigability
• Ease of use
• Reduction of repetitive input which helps to cut down on errors
• Locate errors more easily

 Too often people dive into creating a spreadsheet without a thought as to the end product. How many of you have had to cut and paste or insert rows and columns for data that needed to be moved later or to make room for data that had not considered when you started? I would be surprised if everyone reading this failed to raise their hand!

Before touching that keyboard consider the following:

• Who will be using the spreadsheet?
• What is the purpose of the spreadsheet?
• What are the required inputs?
• What mathematical expressions are you thinking about using?
• What are the desired outputs?
• How are you going to test the model?

It is highly recommended that after considering the questions above, you sketch out mentally or on paper how the spreadsheet should be laid out. You should have a minimum of 3 separate modules or areas and usually you will have at least 4.  These days, rather than modules, think of sheets within the Excel file.

• Input Area All the user inputs and defined variables (constants) Including assumptions and documentation
• Work Area Calculations The raw data and formulas to process the inputs – nitty gritty stuff
• Output Summarization and display of data
• Reports Charts and tables -(Presentation)

  

Clearly you could have a number of modules in the work area depending upon the complexity of the problem you are examining. You should plan on a number of modules in your work area if you anticipate a complex spreadsheet.

 Design Points

As you begin to design a spreadsheet you need to consider the following design points

Documentation

Yes- that old thing. Even if you hate to document what is going on with the spreadsheet, at least put the date, your name and the title of the worksheet or project. No one ever thinks they have the time to sit down and document but at least if you set a standard design with an assumptions section you will have a starting point later.

For everyone who says they have no time to document consider how much time you have spent staring a spreadsheet that you created 7 or 8 months before wondering what the heck that number is or what the purpose of a cell’s formula is. Admit it – we all have. I have one individual who admits every time budgets roll around he cringes because he knows that he will have to spend time figuring out what he did the previous year because he didn’t bother to document it in the worksheet and he can never find those little scraps of paper with the notes he made at the time.

Natural Flow

Ensure there is a natural flow of information down each worksheet instead of having to jump forwards, backwards and sidewards.

Headings

Excel in many cases assumes that there is a heading above the raw data. It also makes things much easier to read. Bolding the headings increases readability too as opposed to italicizing which is often difficult to read.

  

Blank Rows and Columns

  

• Blank rows and columns seem to be a favorite among accountants for a cleaner look; however, be aware that a lot of Excels built-in features assume a blank row or column means no more data. In other words do not break up data that you may need to sort or filter with blank rows.

•  For design purposes some people recommend leaving at least 4 blank rows above your headings and data as these can be used for viewing Totals instead of having to scroll down the bottom of a large spreadsheet. Also blank rows at the top of a spreadsheet are useful for criteria for database functions and the advanced filter feature.

  

Absolute Cell References

• Use absolute cell references wherever possible. It is much easier to change a single cell and have the impact ripple through the spreadsheet than to have to find the cell, change it and then copy it across or down and then view the impact. I think that you can also see how changing just one number is more efficient and would help to reduce potential errors.

•   If you do use absolute cell references set them up in a separate section so that they are easier to locate.

Cell References

• Always use cell references instead of typing in numbers. It increases readability and helps reduce errors and repetitive data entry.

•  It is also helpful to have input cells in the same column – reduces errors in referencing them in formulas.

Sorting

If possible, sort your data as many Excel functions such as Vlookup assume that your data is sorted.

Dates

Ozgrid.com recommends using real dates for headings and to format them appropriately. By this I mean if you want the names of the months as headings type them in as 1/1/2001, 1/2/2001, 1/3/2001 etc then format them as "mmmm". This is a very simple procedure that is all too often overlooked by many. If you have real dates as headings life will be much easier further down the road if you need to use them in formulas. Iff you don't need them -nothing is lost.

Use Single Cells

Keep formulas simple.
Don't put in one cell what could go in more than one cell. Say you have the names of 100 people to put into your spreadsheet; don’t put their full name in one cell. Instead, put the First name in one cell and their surname in the next cell to the right. If you need to place them into one single cell at a later stage, this can be done very easily by concatenating them; however, the same cannot always be said for the reverse.

Break Model Down

If the spreadsheet is complex, large or has a lot of linked cells break it down into manageable components. Errors increase exponentially as the complexity of the model increases. The frequency of use also impacts the potential error rate.

Test your spreadsheet

Test your model before you begin using it! 

• Test the formulas 
•   Use error checking formulas in adjacent cells – IF functions can be helpful
•  Test range names if you have used them
•  Test formulas and ensure they were copied correctly
•  Check the effects of rounding and number formatting
•  If using unusual functions check to make sure they were correctly applied.
• Test the charts and reports
• Compare results of data to previous versions or prior periods

 And test it again, after you make changes to it. Don't use the same spreadsheet year after year without testing it periodically after your have made revisions.
This last is an important point, that many people forget about - to their regret!

Paste Link as a Picture

Everyone is always trying to get as much information as possible onto a page particularly if they are creating a dashboard.

This is where the Camera Tool and the Paste As Picture> Paste Picture Link features come in. The Camera Tool and the Link Feature are absolutely cool features that allow you to put a lot of information on a single page. It is extremely useful in allowing you to create small manageable charts.

These features actually take a linked picture of your data or your chart so that when you paste it in a different location, you are creating an exact “picture” that is linked to the original data. When the original data changes so does the picture. Think of it as a living picture such as what you see in the Harry Potter movies.
You can size and move the picture and place it exactly where you want it without any impact to the original. The best part of this is that since it is a picture the pixel ratios stay intact when you resize it.

The most frequent reason I use these features is that it allows me to position a picture of a chart or data in my report while ignoring the row and column widths in the destination file.

To access the Camera Tool button

1. Click on the Office Button
2. Click on Excel Options
3. Click Customize
4. Click the Chooses commands from: drop-down arrow and select Commands Not in the Ribbon
5. Select the Camera Tool in the first column
6. Click the Add icon to add it to the Quick Access Toolbar (QAT)
7. Click OK.

The Paste as Link Picture does the same thing as the Camera Tool. Now, I personally prefer the Paste as Link Picture feature and although people will argue I think it is faster than using the Camera Tool. However, if you have an earlier version of Excel then you have to use the Camera Tool so I thought I would cover both here.

To use Paste as Link Picture:
1.Copy your chart or data as you normally would
2. Click on the destination cell
3. Select Paste> Paste as Picture>Paste Picture Link

 
You can then move and size the object as you wish.
Since the picture is linked to the original data - any change made in the source is automatically updated in the picture.
 
 
 
 
 
 
Don't forget - you can use this in Microsoft Word and PowerPoint too.

 

Go To Special

 Go To Special feature can be very useful in a variety of ways.
Many of the selections are pretty self explanatory. For instance, you can use it to jump to comments in a spreadsheet as well as formulas and errors. If you use the audit features in Excel, you can have it immediately jump  to precedent or dependent cells.

One that you may not see a use for is Visible Cells Only.
If you subtotal data and copy it elsewhere, all the data pastes(even the hidden data).
Okay- see where I am going with this?
If you select your subtotals and then select the Go To Special dialog box and select Visible Cells Only - if you then select Copy- only the visible cells are copied. So, when you Paste, only the visible subtotals are pasted.

Finding Conditional formats and data validations are also useful.

In Excel 2003, press F5 and it will take you to the Go To dialog box and the Special button is at the bottom of the dialog box. You can also find in under the Edit menu.
In Excel 2007, select Find and Select icon on the Home Tab and you will see Go To Special....

Hey, it's Friday. Have a great weekend.

Range Names at Worksheet Level

Range Names at Worksheet Level

I was putting together some information on range names and realized that when I posted my information on range names (under Basic Tips) that I neglected to mention a new feature in Excel 2007.
If you create a range name using the Name Manager icon, you can specify if the range name should be at the workbook scope (this is the default) or you can specify a specific sheet for that range name to be used on. In other words, in Excel 2007, you can actually use a range name more than once as long as it is at sheet level. If you do create a range name at the sheet level, it is only recognized on that particular sheet.

Click on the Formula Ribbon and then go to Name Manager and click New...
Type in a range name and then click the drop-down arrow beside Scope if you want it to be sheet specific. As I mentioned earlier, the default is workbook level.

In all other versions of Excel, you can only use a range name once in a workbook.

Keyboard Shortcuts

Sorry- I skipped yesterday - not on purpose... just lost track of time. And guess what? - I don't have a lot of time today either. Unfortunately, nothing exciting - just trying to complete the training manuals I am using at the the Indiana CPA Society next month. One course is on Advanced Tools for Analysis and the other is Timesavers for Experienced Users.

Anyway, I wanted to talk about the basics - keyboard shortcuts. If you are like me, you learned some of them in an intro Excel course ..oh so long ago and then promptly forgot them. Well, let me tell you the control key and the shift key are two that you don't want to forget.

CONTROL KEY
Selecting non-contiguous rows can be useful for formatting. For instance, if you find yourself formatting the top row and then the bottom row of your income statement as currency then this technique will be useful for you.

Selecting non-contiguous rows of data is easy. Simply select the first row of data as you normally would and then hold down the CTRL key on the keyboard and then select the next row of data. You can repeat this multiple times.

SHIFT KEY
If you’ve selected too much or too little, simply hold down the Shift Key and then use the arrows on the keyboard to add or reduce cells. The Shift Key locks you into place. This also works in Microsoft Word.

If you know the size of the data you want to select , you can click on the 1st cell that you want to select, hold down the Shift Key, and then click the last cell in the range and Excel will select everything in between automatically. In this example, if I click on Cell A1, hold down the Shift key and select C3, all the cells in between are selected.

By the way, these shortcuts also work in Microsoft Word.
Talk to you tomorrow.

Excel Tip- Range Names

Range Names as Absolutes

I decided to talk about Range Names today since my blog of yesterday was on absolute cell references. Whenever you name a cell, Excel treats it as an absolute cell reference. So, let's talk about this a bit and see why it can be extremely useful to give a cell or a range of cells a name.

• First, you can use range names in formulas. =Sum(expenses) is a lot more explanatory as well as easier to remember than =sum(F15.F36). This can be very useful in those budget spreadsheets that you use a couple of times a year and have trouble remembering what things are.



• You can use range names to navigate around a workbook as a range name can only be used once in a book. Select the range name and Excel will automatically hop you over to the cell or range of cells that you named - even if they are on a different sheet.



• You can use a range name in a print area so if you are printing different sections of the same file every month it would be easier to remember a range name than cell references.

Okay- you get the idea. I personally like to use range names in Vlookups and other functions where I need to refer to a table - particularly if I am going to copy that function down. Why? Because range names are absolute cell references and if I incorporate the range name into the Vlookup, I don't have to worry about making sure that the cell references are absolutes cell references - it does it automatically!! Honest....

So, now that I have convinced you of your need to know about range names, let me tell you how to create one. There are a couple of ways but this works in all Excel versions and frankly it is the easiest way to do it so I won't bore you with the other ways.

1. Select the cell or range of cells that you want to name.


2. Click the Name box, located at the left end of the formula bar, just above Column A .


3. Type in the name that you want to use.


4. Press the ENTER key.

I told you it was easy!

Okay.. now for the rules...
Names can be up to 255 characters but most people keep them very short so that it is easy to remember and easy to type.
Range names cannot begin with a number ( 2008 Sales is won't work but Y2008_Sales would )
Range names cannot contain any spaces. (You can use underscores though)

• Careful- in Excel 2007, some range names such as Tax08 won’t be valid range names as it is considered a cell address due to the size of the 2007 Excel spreadsheet!

Okay- last thing... if you want to select the Range Name simply click the drop down arrow beside the Name box.
If you want to see the range names or use them in a formula, click the F3 key to provide a list of them. (In Excel 2007, you can also use the Name Manager found on the Formula Ribbon).

Excel Tip - Absolute Cell References

Absolute Cell Reference

I wanted to start with some real basics as a lot of Excel websites tend to skip them. Probably one of the most important things everyone needs to understand is the difference between an absolute cell reference and a relative cell reference. The default in Excel is a relative cell reference.




Think about it, if you copied the formula of A2*B2 as shown in the example, you don't expect to get the answer of 3 everytime do you? Of course not, instead you just assume that Excel is smart enough to know that when you copied the formula to row 3, Excel should adjust the formula to its new relative position and the new formula becomes A3*B3 with the resulting answer of 6 without you having to do anything. That is relative cell referencing.







Sometimes though, you don't want Excel to automatically adjust the formula and that is where the trouble comes in as you need to tell Excel that by specifying the cell is an absolute cell reference. Absolute cell references become very important if you are referencing an input cell, doing percentages or using functions such as Vlookup.



Let's change the example above slightly and use an input cell. Notice that the price for all of these is the same. It would be much more efficient to create a price input cell and reference that. I have done that in the next example. My input cell is E1. Then in Cell B2 I created a formula of =A2*E1. Okay, now if I just go and copy that down to row 3, I am going to end up with =A3*E2. Now, I do want Excel to "pick up" A3 but cell E2 has nothing in it and I will get an error. By using dollar signs around E1 in the formula, it tells Excel to freeze and always refer to cell E1. This makes E1 an absolute cell reference. So, if you go and create this and take a look at cell B4, you should see =A4*$E$1 with the resulting answer of 9.Because the dollar sign is in front of the column heading and the row heading, it knows that it must refer only to Column E and it must refer only to row 1. This is absolute cell referencing.






You can just type the dollar signs in or after typing in the cell reference E1, press the F4 key on your keyboard.

Most of the time, people don't think about absolute cell references so here is a handy tip - always check your formulas when you copy and don't just check the first one- check the 3rd or 4th one to make sure that your formula is actually doing what you wanted. If it is not working, chances are you need to make a cell or cells absolute.

I hope this made sense to everyone. If you have any questions, email me at patricia@cpaselfstudy.com