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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies