Home

Hiding Data in Excel - Need help!

%3CLINGO-SUB%20id%3D%22lingo-sub-733981%22%20slang%3D%22en-US%22%3EHiding%20Data%20in%20Excel%20-%20Need%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-733981%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20a%20current%20spreadsheet%20I%20am%20working%20on%20-%20it%20is%20to%20record%20book%20sales.%20All%20the%20repeating%20data%2C%20I%20want%20to%20make%20blank%20until%20the%20blank%20areas%20are%20filled%20in.%20Is%20this%20possible%3F%20I%20have%20tried%20the%20conditional%20formatting%20and%20turning%20all%20repeating%20data%20to%20white%2C%20however%20that%20messes%20with%20the%20inventory%20count%20so%20the%20inventory%20count%20in%20the%20row%20that%20I%20am%20working%20on%2C%20doesn't%20change%20until%20I%20change%20it%20in%20the%20next%20row.%20If%20that%20makes%20sense.%20Any%20help%20is%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-733981%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESpreasheets%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-735541%22%20slang%3D%22en-US%22%3ERe%3A%20Hiding%20Data%20in%20Excel%20-%20Need%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-735541%22%20slang%3D%22en-US%22%3E%3CP%3EHey%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F369887%22%20target%3D%22_blank%22%3E%40Sioren%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20You%20can%20create%20another%20column%20to%20identify%20whether%20or%20not%20the%20number%20in%20the%20original%20column%20is%20a%20duplicate%20or%20not%20by%20using%20this%20formula%3A%26nbsp%3B%3DIF(COUNTIF(%24A%241%3A%24A1%2CA1)%26gt%3B1%2C%22Duplicate%22%2C%22No%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122011i993D017D6415CA68%2Fimage-size%2Fsmall%3Fv%3D1.0%26amp%3Bpx%3D200%22%20alt%3D%221.PNG%22%20title%3D%221.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20You%20can%20then%20select%20this%20column%2C%20use%20the%20Find%20option%20in%20the%20Home%20Tab%20to%20delete%20all%20the%20Values%20that%20say%20%22Duplicate%22%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122012i667BA8105224B875%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%222.PNG%22%20title%3D%222.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B3)%20This%20should%20you%20a%20list%20of%20cells%20that%20contain%20the%20value%20%22Duplicate%22.%20Select%20all%20of%20them%20by%20holding%20down%20the%20shift%20key%20and%20clicking%20the%20last%20row.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122014i684A5F3E6E5EA6EC%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%223.PNG%22%20title%3D%223.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E4)%20Then%20Press%20Close%20and%20all%20the%20cells%20that%20contain%20the%20value%20%22Duplicate%22%20should%20be%20highlighted%3C%2FP%3E%3CP%3E5)%20Finally%2C%20press%20Delete%20and%20Hide%20column%20E.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20201px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122015i95A9CF59D216B425%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%224.PNG%22%20title%3D%224.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-735766%22%20slang%3D%22en-US%22%3ERe%3A%20Hiding%20Data%20in%20Excel%20-%20Need%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-735766%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F369887%22%20target%3D%22_blank%22%3E%40Sioren%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20hide%20repeating%20items%20with%20conditional%20formatting%20and%20count%20only%20unique%20inventory%20numbers%20with%20one%20of%20the%20formulas%20from%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Fcount-unique-values-in-a-range-with-countif%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fformula%2Fcount-unique-values-in-a-range-with-countif%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Sioren
Occasional Visitor

This is a current spreadsheet I am working on - it is to record book sales. All the repeating data, I want to make blank until the blank areas are filled in. Is this possible? I have tried the conditional formatting and turning all repeating data to white, however that messes with the inventory count so the inventory count in the row that I am working on, doesn't change until I change it in the next row. If that makes sense. Any help is appreciated!

2 Replies

Hey @Sioren ,

 

1) You can create another column to identify whether or not the number in the original column is a duplicate or not by using this formula: =IF(COUNTIF($A$1:$A1,A1)>1,"Duplicate","No")

 

1.PNG

 

2) You can then select this column, use the Find option in the Home Tab to delete all the Values that say "Duplicate":

2.PNG

 3) This should you a list of cells that contain the value "Duplicate". Select all of them by holding down the shift key and clicking the last row. 

3.PNG

4) Then Press Close and all the cells that contain the value "Duplicate" should be highlighted

5) Finally, press Delete and Hide column E.

4.PNG

 

 

 

 

 

@Sioren 

 

You may hide repeating items with conditional formatting and count only unique inventory numbers with one of the formulas from here https://exceljet.net/formula/count-unique-values-in-a-range-with-countif