Google Sheets waa mid ka mid ah alaabada ugu awooda badan Google waana mid ka mid ah xalinta xaashida faafinta iyo maamulka xogta ee shabakada. Si kastaba ha ahaatee, sanduuqa ka baxsan, waxay leedahay qodobbo daciif ah - mid aan kaga hadli doono maqaalkan.

Nasiib wanaag, Sheets waxay taageertaa hawlaha gaarka ah iyo qaacidooyinka ka caawiya inay ka soo baxaan qaar ka mid ah cilladahaas. Haddii isticmaalayaashu ay ku xeeldheer yihiin qaacidooyinka ama ay samayn karaan wax yar oo qoraal^(scripting) ah , Xaashiyadu waxay noqon karaan kuwo awood leh sida ay u sameeyaan.

Mid ka mid ah shaqada gaarka ah ee Sheets-ku aanu asal ahaan taageerin waa awooda lagu helo nuqul ka mid ah xaashiyaha Google^{(Google Sheets)} - hadday tahay xogta nuqul ka mid ah unugyada ama safafka. In kasta oo ay jirto hab si deg deg ah meesha looga saaro gelinta nuqul ka mid ah, iyadoo la isticmaalayo ka saar nuqul^{(Remove duplicates)} ka mid ah ikhtiyaarka ka hoos menu Data , ma jirto si ay oo kaliya in la go'doomiyo kuwa nuqul ka mid ah. Akhbaarta wanaagsan ayaa ah in ay jirto hab lagu gaaro tan.

Maqaalkan, aan dulmarno sida loogu heli karo nuqul ka mid ah xaashiyaha Google^{(Google Sheets)} -ka anagoo ka faa'iidaysanayna qaabaynta shuruudaha.

Waa maxay Qaabaynta Shuruudaha ah ee Xaashiyaha Google?^{(What Is Conditional Formatting In Google Sheets?)}

Qaabaynta shuruuda ah waa sifo ku jirta tafatirayaasha xaashida faafinta badan taas oo u ogolaanaysa isticmaaluhu inuu ku dabaqo qaab qoraal ku salaysan iyo qaab muuqaal kale unug ku salaysan xaalado gaar ah.

Tusaale ahaan, isticmaaluhu waxa laga yaabaa inuu diiwaangeliyo miisaaniyadiisa billaha ah isagoo isticmaalaya xaashida xaashida, isagoo raba inuu jaleeco haddii ay dhaafaan $500 oo kharash ah. Tiirka xaashida xaashida ah ee wadarta bisha la tiriyo, xeerka habaynta shuruudaysan ayaa lagu dejin karaa safafka geesinimada leh ee qiimahoodu ka badan yahay $500, taasoo sahlaysa in la arko halka ay tani ka dhacday.

Xaashiyaha Google^{(Google Sheets)} - ka , qaabaynta shuruudu waxay u ogolaataa isticmaalayaasha inay codsadaan qaabaynta iyadoo lagu salaynayo liis dheer oo qawaaniin hore loo dajiyay ama iyagoo galaya qaacido gaar ah. Qaabka qaabaynta isbeddelada ee la heli karo waxaa ka mid ah dhiirranaanta, farta xarriiqda, hoosta ka xarriiqda, midabka qoraalka, iyo midabka unugga.

Intaa waxaa dheer, isticmaalayaashu waxay isticmaali karaan miisaanka midabka, halkii ay ka isticmaali lahaayeen hal midab, oo waxay qeexi karaan qiimaha ugu yar iyo kan ugu sarreeya, iyo midabkooda, midabaynta tooska ah.

Sida Loo Isticmaalo Qaabaynta Shuruudaha Ah Si Loo Helo Safka Kooban Ee Xaashiyaha Google-ka^{(How To Use Conditional Formatting to Find Duplicate Rows In Google Sheets)}

Qof kasta oo ka shaqeeya xaashida dheer ee xogta, gelida nuqulku waxay noqon kartaa dhibaato marmar jebin doonta socodka waxyaabaha. Isticmaalayaashu waxa laga yaabaa inaanay si fudud u rabin inay tirtiraan xogtan, oo Sheets ay si asal ahaan u taageerto, oo ay u falanqeeyaan oo ay la shaqeeyaan.

Isticmaalka qaacido gaar ah oo qaab shuruudaysan leh, tani waa suurtogal. Talaabada ugu horeysa ee tan lagu gaarayo waa in la muujiyo dhammaan tiirka u baahan in la raadiyo nuqul ka mid ah adigoo gujinaya calaamadda tiirka ee ka sarreeya.

Marka la iftiimiyo, midig-guji oo dooro qaabaynta shuruuda^{(Conditional formatting)} ah. Tani waxay keenaysaa guddi dhinaca midig ee daaqadda ku yaal oo tusinaya shuruuc qaabaynta shuruudaysan. Kala duwanaanshaha tiirka ee la doortay ayaa horay loo buuxin doonaa Codso^{(Apply to range)} goobta kala duwan, markaas uma baahna in la taabto.

Marka xigta, waxaad dooneysaa inaad gujiso liiska hoos -u-dhaca ee hoos yimaada xeerarka Qaabka^{(Format rules)} si aad u bedesho xaaladdan qaacidada gaarka ah waa^{(Custom formula is)} . Meel qoraal ah ayaa ka soo bixi doonta liiska hoos-u-dhaca ka dib marka tan la sameeyo, waana in la geliyo qaacidada soo socota:

=countif(A:A,A1)>1

Si kastaba ha ahaatee, waxay u baahan doontaa in wax laga beddelo si loogu beddelo dhammaan tusaalooyinka "A" xarafka tiirka ee lagu raadinayo nuqullada Google Sheets , kaas oo lagu muujin doono Codso^{(Apply to range)} goobta kala duwan.

Talaabada u dambaysa waa in la dejiyo qaabaynta si loogu dabaqo dhammaan unugyada safkan oo ay ku jiraan xogta la helay in ka badan hal mar. Haddii lagu shaqeeyo xog badan, qaabka ugu fiican ee la dejin karaa wuxuu noqon karaa midab buuxa. Isticmaalka, midabka dhalaalaya oo la dareemi karo sida jaalaha, casaanka kulul, ama cagaarka lime ayaa dhab ahaantii istaagi doona oo u oggolaanaya in la ogaado nuqul ka mid ah jaleecada marka la wareegayo.

Hadda, dhammaan safafka ay ku jiraan xogta laga helo ugu yaraan hal saf oo kale ayaa la iftiimin doonaa ama loo qaabayn doonaa qaab kasta oo la doortay. Haddii safafka nuqul ka mid ah la tirtiro, safka keliya ee soo haray waxaa si toos ah meesha looga saarayaa qaabeynta shuruudaha.

Mid ayaa laga yaabaa in la weydiiyo, "Maxaa dhibkan ugu gudbaya marka Google uu taageerayo saarista nuqul ka mid ah laba dhagsi oo fudud?" Waa hagaag, nidaamkani waa mid aan dabacsanayn oo aan qummanayn. Tusaale ahaan, ma jirto si loo beddelo nuqulka la tirtiray. Isticmaalayaasha qaar ayaa laga yaabaa inay rabaan in tusaalaha ugu horreeya la tirtiro, iyo kuwa ugu dambeeya - Google ma bixiyo wax ikhtiyaar ah oo wuxuu saaraa dhammaan nuqullada laakiin kan ugu horreeya.

Isticmaalka xeerkan fudud ee qaabaynta shuruudaysan, isticmaaluhu waxa uu si buuxda gacanta ugu hayaa maaraynta nuqullada si aan khatar u gelinayn jebinta xaashida. Waxa ugu wanagsan waa in qaabaynta shuruudu ay gabi ahaanba tahay mid firfircoon, markaa waligaa uma baahnid inaad meesha ka saarto qaabaynta haddii aad doorato inaad si uun uga saarto dhammaan nuqulada.

Si kastaba ha ahaatee, waa in xeerka qaabaynta shuruuduhu waligiis u baahan yahay in la tirtiro, way fududahay sida adigoo gujinaya Format menu ka dibna qaabaynta shuruuda^{(Conditional formatting)} ah , kaas oo muujin doona isla menu dhinaca midig ah sidii hore. Isticmaalayaashu waxay ku dul haadin karaan curcurkooda safka muujinaya qaacidada gaarka ah ee aan hadda abuurnay oo guji astaanta qashinka ee u muuqata inay meesha ka saarayso.

Marka Google Sheets 'doorashada hooyo ee ah in la helo nuqul ka mid ah si ay meesha uga saarto kaliya maaha mid awood leh oo ku filan ama u xoqaya xogta si aan la awoodin, qaacidada caadiga ah ee fudud ee lagu muujiyay qodobkan ayaa u oggolaanaysa isticmaalayaasha inay gacmahooda ku qaataan arrimaha.

Ma haysaa wax su'aalo ah ama faallooyin ah oo ku saabsan isticmaalka xeerka qaabaynta shuruudaysan? Haddii ay sidaas tahay, fadlan noogu dhaaf faallooyinka hoose.

How to Find Duplicates In Rows And Cells In Google Sheets

Google Sheets is one of Google’s most powerful products and one of the go-to solutions for spreadsheet and data management on the web. However, out of the box, it has its weak points—one that we’ll discuss in this article.

Luckily, Sheets supports custom functions and formulas that help make up for some of these shortcomings. If users are savvy with formulas or can do a little bit of scripting, Sheets can be as powerful as they make it.

One particular functionality that Sheets doesn’t natively support is the ability to find duplicates in Google Sheets – whether it’s duplicate data in cells or rows. While there is a way to quickly remove duplicate entries, using the Remove duplicates option under the Data menu, there’s no way to only isolate those duplicates. The good news is that there is a way to accomplish this.

In this article, let’s go over how to find duplicates in Google Sheets by taking advantage of conditional formatting.

What Is Conditional Formatting In Google Sheets?

Conditional formatting is a feature present in many spreadsheet editors which allows the user to apply text-based and other visual formatting to a cell based on certain conditions.

For example, a user may be recording their monthly budgeting using a spreadsheet, wanting to know at a glance if they go over a $500 spending limit. In the spreadsheet column where the total for the month is tallied, a conditional formatting rule can be set to bold rows with a value over $500, making it easy to see where this occurred.

In Google Sheets, conditional formatting allows users to apply formatting based on a long list of preset rules or by entering a custom formula. The formatting style changes available include bold, italics, underline, strikethrough, text color, and cell color.

Additionally, users can use a color scale, instead of a single color, and define a minimum and maximum value, and their respective colors, for automatic color scaling.

How To Use Conditional Formatting to Find Duplicate Rows In Google Sheets

For anyone working on a lengthy sheet of data, duplicate entries can become a problem that will sometimes break the flow of things. Users may not simply want to delete this data, which Sheets natively supports, and instead analyze and work with it.

Using a custom formula with conditional formatting, this is possible. The first step to achieve this is to highlight the entire column that needs to be searched for duplicates by clicking on the column label above it.

Once highlighted, right-click and select Conditional formatting. This will bring up a panel on the right side of the window that shows conditional formatting rules. The column range selected will be prefilled in the Apply to range field, so that won’t need to be touched.

Next, you want to click on the dropdown menu beneath Format rules to change this condition to Custom formula is. A text field will appear beneath the dropdown menu after this is done, and the following formula should be entered:

=countif(A:A,A1)>1

However, it will need to be modified to replace all instances of “A” with the column letter that is being searched for duplicates in Google Sheets, which will be shown in the Apply to range field.

The final step is to set the formatting to apply to all cells in this row that contain data found more than once. If working with a large set of data, the best formatting to set might be a fill color. Using it, a bright and noticeable color such as yellow, hot pink, or lime green will really stand out and allow for noticing duplicates at a glance while scrolling.

Now, all rows that contain data that is found in at least one other row will be highlighted or formatted in whatever way was chosen. If duplicate rows are deleted, the single row remaining will have its conditional formatting removed automatically.

One might ask, “Why go through this trouble when Google supports duplicate removal in two simple clicks?” Well, this system is inflexible and imperfect. For example, there is no way to change which duplicate is deleted. Some users may want the first instance deleted, and others the last—Google gives no option and removes all duplicates but the very first.

Using this simple conditional formatting rule, the user is in complete control of managing duplicates in a way that won’t risk breaking their sheet. Best of all is that conditional formatting is entirely dynamic, so you’ll never need to remove formatting if you choose to remove all duplicates in some way.

However, should the conditional formatting rule ever need to be deleted, it’s as simple as clicking on the Format menu and then Conditional formatting, which will show the same right-side menu as before. Users can hover their cursor over the row that shows the custom formula we’ve just created and click on the trash can icon that appears to remove it.

When Google Sheets’ native option to find duplicates to remove them just isn’t powerful enough or scrambles data in a way that can’t be afforded, the simple custom formula highlighted in this article allows users to take matters into their own hands.

Do you have any questions or comments about using this conditional formatting rule? If so, please leave us a comment below.

Related posts

• Sida loo soo dejiyo Google Chrome Offline (Standalone) Rakibaye

• Sida Muusiga loogu daro Google Slides

• Sida Loo Sameeyo Foomka Dukumentiga Google-ka La Buuxin Karo

• Sida loo beddelo midabka asalka ah ee Google Docs

• Sida loo habeeyo oo loo maamulo xasuusiyayaasha Google

• Deji taariikhda uu dhacayo faylalka Google Drive ee la wadaago

• Sida loo xannibo mareegaha Google Chrome

• Sida loo hagaajiyo Discover Google oo aan ku shaqayn Android

• Sida loogu daro faylalka GPX Khariidadaha Google-ka

• Sida loo isticmaalo Google Assistant Windows 10

• Sida Loogu Raro Google Authenticator Taleefan Cusub Adoon Lumin Gelitaanka

• 5 siyaabood oo aad Google Calendar ugu heli karto Desktop-kaaga

• Sida Xogtaga Shakhsiyeed looga soo dejiyo Google

• Sida HTML Loogu Geliyo Google Doc

• Sida Shaxda Qoraalka Google Docs u Shaqeeyo

• Sida Loo Beddelayo Margins & Boos labanlaab ah Google Docs

• Sida Loola Xidhiidho Google Calendar Iyo Muuqaalka

• Sida Loo Joojiyo Xayeysiiska Shakhsi ahaaneed iyo Natiijooyinka Raadinta ee Google iyo Facebook

• Sida loogu daro jaantuska Gantt gudaha Google Docs

• Beddel Goobta Faylka ee Google Chrome ka soo dejinta