/* old script */ /*added 30-11-14 */

Absolutely PC - Making I.T. working for you

call us on: 0117 975 9523

sign up to our newsletter

Full Name (required)

Company

Your Email (required)

Please see our privacy policy

Menu

Excel – using the IF function to tidy up your spreadsheet

This tutorial is intended for the beginner in Microsoft Excel and contains information that will help to remove unwanted zeros and error messages within the spreadsheet. Part of the tutorial uses the fill down command which is demonstrated here  if you’re unsure how to use it.    

Using functions and equations in Microsoft Excel is a great way of speeding up and automating data entry. However, you often find that by using equations your spreadsheet starts to have zeros or errors where data has not yet been populated. One way around this problem is the use of the “IF function” which does a basic test to determine what data to display on the spreadsheet.    

Microsoft Excel with lots of zeros

When using functions in Microsoft Excel, zeros start to appear where no data has yet been added

The IF function has three sections to it.    

=IF(the test, do this if true, do this if false)  

  1. The first is called a logical test and checks to see whether something is true or false.
  2. The second section is a statement of what to do if the test is true.
  3. The final section is a statement of what to do if the test is false.  

In the video above I demonstrate the use of this function to remove the additional zeros that have appeared in the spreadsheet as a result of no data being available in the first two columns. The way this is done is as follows:    

The logical test that can be done can be as simple or complex as you wish, on this occasion I am only interested as to whether column A contains data or not. If column A is empty I would like column C to be empty but if column A contains any number I want to column C to perform the calculation and add up the numbers of the two previous cells.  

As a result the first part of the function is as follows:      

=IF(A2=””,       

The second part of the IF function in a statement of what is to be done if the test is true. On this occasion if column A contains no data I want columns C to be empty and I can indicate this by using the double quote marks as follows:       

“”

Using Microsoft Excel IF function to remove excess zeros from a spreadsheet

By using the IF function additional zeros have been removed from column C

The final part of the IF function is a statement of what is to be done if the test is false. On this occasion if column A contains some data I want column C to continue to add up the previous two cells, hence the formula:        

A2+B2        

So the entire IF function looks like this:        

=IF(A2=””,””,A2+B2)        

As a result the zeros in the spreadsheet have now been replaced by blank cells until such point the data is entered into the A column.


This entry was posted in Microsoft Excel, Tips and tagged , , , , , , , , , , , , , . Bookmark the permalink.

One Response to Excel – using the IF function to tidy up your spreadsheet

  1. Pingback: Microsoft Excel, using the fill down command - Absolutely PC

Leave a Reply