# how do i get excel to add 0s???

#### CThai

##### Active Member
i have a workbook with missing dates and would like excel to insert any missing dates and add 0's into that missing dates if there is no data...

for example
Book1
ABCD
1DateCompany NameTotal
21/2/2006A25
31/2/2006A35
41/3/2006B125
51/5/2006B20
61/5/2006B185
71/7/2006A200
81/8/2006B250
91/10/2006B250
101/10/2006A350
Sheet1

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### CThai

##### Active Member
with the above image - i would like to insert missing dates with 0 in total and also put in company's name --... so on date 1/2/2006 only company A name have total I would like it to recognize that company B didn't have total and insert Company B into 1/2/2006 and put 0's in there so that i can average it out and graph it accordingly...

#### QuietRiot

##### Well-known Member
so for 1/3/2006.. there is no company A

would it stick that in there and add zeros?

#### CThai

##### Active Member
Yes - if the company A or B have no total it would stick
1) the company's name
2) 0's data and
3) dates...

#### CThai

##### Active Member
So if company A have a total on 1/2/2006 and company B dont - then insert 1/2/2006 Company B with 0's in the total...

is this even possible?? i'm asking excel to insert not only the dates but the company names and the total - into anything that missing data going back since 1999...

is there light at the end of the tunnel??

#### CThai

##### MrExcel MVP
So if company A have a total on 1/2/2006 and company B dont - then insert 1/2/2006 Company B with 0's in the total...

is this even possible?? i'm asking excel to insert not only the dates but the company names and the total - into anything that missing data going back since 1999...

is there light at the end of the tunnel??

The light is to have formulas to treat missing data as missing, that is, as zeroes. They most often do so automatically...

#### CThai

##### Active Member

The light is to have formulas to treat missing data as missing, that is, as zeroes. They most often do so automatically...

not sure what you mean? would it show up as 0 (zeor) since i will be using this to graph...

##### MrExcel MVP

The light is to have formulas to treat missing data as missing, that is, as zeroes. They most often do so automatically...

not sure what you mean? would it show up as 0 (zeor) since i will be using this to graph...

You could AutoFilter the data area with custom: >0 OR <0, then build the graph.

#### CThai

##### Active Member
You could AutoFilter the data area with custom: >0 OR <0, then build the graph.

I autofilter the data but still not getting 0 (zeros) to show up on the missing data... is there something else i have to do?

Replies
5
Views
228
Replies
1
Views
145
Replies
2
Views
194
Replies
5
Views
177
Replies
13
Views
399

1,191,125
Messages
5,984,786
Members
439,911
Latest member
dk73

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back