0

An excel sheet having rows and columns, each cell accept 1 to 6 numeric values. How to check 1000 columns? what are the technique following for this?

  • 2
    Hello Mahesh, could you be more specific? I'm not able to understand what exactly are you trying to do! Give some more info... – IAmMilinPatel Oct 2 '14 at 11:48
  • u have to check 1000 cells, each cell has accept the values between 1 to 6. how to check? Either check each cell by giving input between 1 to 6 or you can apply any technique for this....? – MAHESH S Oct 3 '14 at 13:59
3

To check that cells in excel sheet contain values between 1 and 6 do the following,

  1. Select all the cells that you want to check.
  2. the header of excel click in Conditional Formatting.
  3. In the drop down menu that appears click on new rule.
  4. A New Formatting Rule window will appear.
  5. Select rule type - Format only cells that contain.
  6. Now look at the edit rule description section.
  7. In Format only cells with section leave first 2 options selected as Cell Value and Between.
  8. In the next 2 fields insert 1 and 6.
  9. In preview click on Format button.
  10. In the Format Cells window select color of your choice and click OK button.
  11. Click OK button in New Formatting Rule and all the cells with values between 1 and 6 will get colored with your selected color!!!
  • So now we have 1000 coloured columns, how does that help? :) Although I like the colouring idea, because its easier to scan for an colour then for actual data. Its missing any explanation how and why to use it. I would make two rules. Green data between 1-6 and red for everything larger or smaller then 1-6. This way you can scan for just the red cells. – Niels van Reijmersdal Oct 4 '14 at 7:23
  • 1
    Yes Neils. Having different colors will definitely help. All i did was show how to highlight something. Now more than one rule can be used to highlight it in various ways!!! That is all upto the creativity and curiosity of an individual. Like i did not know of these rules in excel up until 1 month ago. Was facing similar issue n googled to find an answer. I found one, then went on to experiment and make it more usable! – IAmMilinPatel Oct 4 '14 at 7:30
0

Fastest way to check if all cells have a value between 1 and 6 is to use the COUNTIF function. Put it somewhere in your sheet, update the range and verify it returns 0.

=COUNTIF(A1:Z1000,">6")
=COUNTIF(A1:Z1000,"<1")

Now if the result is higher than 0, use the coloring technic in the answer of Milinpatel17 to find the cells that are off.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.