In my Excel (2016) sheet I have a Source sheet which has a Table with all the data of a year. From this table I want individual sheets for each month. I don't want to be copying everything over every time something changes so I created a connection to another sheet with the data for lets say April. There I have the entire Source table which I can filter. All the data is looking good except for the formatting which I've copied over with a copy/paste on the entire table.

Some of this formatting contains conditional formatting and for the most part this is working good except for conditional formatting on smaller than (red) or greater than (green) 0. For some reason all the data is handled as positive, even on visual negative values. At least this is what I think.

The columns that have this behavior are columns with data in the form of minutes (time) as [m] to allow negative minutes to be displayed. In the source table these conditional formattings are working as expected.

Below are the examples. This first image is the Source sheet table. The second image is the April sheet table.

Source table formatting April table formatting

Something else I've noticed is that when I manually fill something in in these columns, the number 1 for example is translated to 1440. The number -1 is translated to -1440 and the conditional formatting is working on this.

Manual data

When checking the formula bar where I put in -1 is actually -1. When looking at where I put 1 I get the following: 1/01/1900 0:00:00.

Have I chosen my source data format wrongly? Should I have been werking with integers instead of time?

Off-topic: My final goal is to display a graph of each month and creating the separate tables was to my knowledge the way to go. If there is a better way to do this feel free to link me to an example.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.