Automatically translated.View original post

25K followers

54 saved

✨ Just type the employee's name, Excel runs the sequence number automatically, ➕ also finishes counting.

✨ Just type in the employee's name, Excel runs the sequence number automatically. ➕ Finish counting how many people there are. ✅

Don't count by name anymore. 🎉

Anyone who works HR or supervises employees must have this technique on their desk!

2025/9/27 Edited to

ถ้าตารางพนักงานของคุณเป็นแบบ “พิมพ์ชื่อเพิ่มเรื่อยๆ” แล้วอยากให้เลขลำดับวิ่งให้เอง (ไม่ต้องลากสูตรทุกครั้ง) แนะนำทำ 2 ส่วนคือ “รันเลข” และ “นับจำนวนพนักงาน” แบบอัตโนมัติค่ะ 1) รันเลขลำดับอัตโนมัติ “ตามข้อมูลชื่อพนักงาน” สมมติคอลัมน์ A = เลขลำดับ, คอลัมน์ B = ชื่อ-นามสกุล (เริ่มที่แถว 2) ใส่สูตรที่ A2 แล้วลากลง: =IF(B2="","",COUNTA($B$2:B2)) แนวคิดคือ ถ้าแถวนี้ไม่มีชื่อ ให้เว้นว่าง แต่ถ้ามีชื่อ ให้ COUNTA นับจำนวนชื่อสะสมตั้งแต่แถวแรกจนถึงแถวปัจจุบัน เลขจะเพิ่มเองทันทีเมื่อพิมพ์ชื่อใหม่ 2) รันเลข Excel “ข้ามช่อง/ข้ามแถวว่าง” ให้เลขไม่กระโดด สูตรด้านบนทำได้อยู่แล้ว เพราะถ้า B2 ว่างจะไม่แสดงเลข และแถวถัดไปที่มีชื่อจะนับต่อเนื่อง (เช่น 1, 2, เว้นว่าง, 3) แต่ถ้าคุณมีข้อมูลอื่นปน เช่นมีช่องว่างจากการคัดลอก หรือมีเว้นบรรทัดในช่วงกลาง สูตรนี้ช่วยให้เลขไม่เพี้ยน 3) ถ้าต้องการรันเลขเฉพาะแถวที่ “มีชื่อ” แต่ชื่ออาจมีช่องว่าง (space) บางทีมีการพิมพ์เว้นวรรคไว้ ทำให้ดูเหมือนว่างแต่จริงๆ มีค่า แนะนำปรับเป็น: =IF(TRIM(B2)="","",COUNTA($B$2:B2)-COUNTIF($B$2:B2,"")) หรือถ้าข้อมูลค่อนข้างสะอาด ใช้ TRIM ในเงื่อนไขก็พอค่ะ 4) สูตรนับ “จำนวนพนักงานทั้งหมด” ให้จบในช่องเดียว ถ้าชื่ออยู่คอลัมน์ B: =COUNTA(B:B)-1 (ลบ 1 กรณีมีหัวตาราง) หรือถ้าต้องการนับเฉพาะช่วงข้อมูลจริง เช่น B2:B2000: =COUNTA(B2:B2000) 5) ทริคสำหรับงาน HR/รายเดือน ถ้าตารางมีหัวข้ออย่าง “จำนวนพนักงาน”, “ยอดเงินเดือน”, “จำนวนอายุงาน”, “ตำแหน่ง” แล้วต้องเพิ่มรายชื่อทุกเดือน แนะนำทำเป็น Excel Table (กด Ctrl+T) ก่อน จากนั้นใช้สูตรแบบ Structured Reference จะลากตามอัตโนมัติและกันสูตรหายเวลาเพิ่มแถว ลองใช้ชุดสูตรนี้แล้วจะรู้สึกว่าการทำรายชื่อพนักงานลื่นขึ้นมาก โดยเฉพาะเวลาต้องอัปเดตข้อมูลประจำเดือนและอยากให้เลขลำดับ + จำนวนคนอัปเดตเองทันทีค่ะ

A person takes a mirror selfie, with the text "COUNTIF FUNCTION" overlaid. The person is wearing glasses and a black and white patterned top, holding a phone. Wavy white lines frame the image.

An Excel-like table displays a list of names under 'Winners' and 'Players' columns. Overlay text explains the goal: to count how many times each name appears in the list.

An Excel screenshot demonstrates the COUNTIF function. The formula `=countif(G4:G23,L4)` is shown, highlighting the range of names and selecting 'Peyton' as the criteria for counting.

COUNT IF EXCEL FORMULA

In the world of Microsoft Excel functions, there's one gem that stands out for its simplicity and effectiveness in data analysis – the COUNTIF function. Whether you're a seasoned Excel user or just dipping your toes into the world of spreadsheets, understanding and harnessing the potential

56 likes

A laptop displaying an Excel-like interface, a gold lamp, and glasses on a desk. The image is overlaid with the title "EXCEL'S COUNTA FUNCTION" in light green text, introducing the topic.

A light green background with bold white text stating: "THE COUNTA FUNCTION COUNTS THE NUMBER OF CELLS THAT ARE NOT EMPTY IN A RANGE." This defines the core purpose of the Excel COUNTA function.

An Excel spreadsheet showing a column of names labeled "Winners." The formula `=COUNTA(G4:G23)` is being entered into a cell, demonstrating how to apply the COUNTA function to count non-empty cells.

EXCELS COUNTA FUNCTION

In the vast realm of Excel, where cells transform into dynamic canvases of data, I recently stumbled upon a tool that revolutionized the way I analyze information – the COUNTA function. Allow me to share my newfound admiration for this versatile gem. COUNTA, short for 'count all,' is not

47 likes

Excel signature

Yes a handwritten signature imported in excel #exceltips #exceltricks #exceltraining #exceltutorial #excel

189 likes

Sequence in excel or google sheets

The =SEQUENCE function in Google Sheets is a game changer for generating numbers automatically. No more typing rows manually. In this quick tutorial I’ll show you how it works and how you can use it in real projects. #googlesheets #exceltips #spreadsheettips #googlesheetstricks #datame

7 likes

Excel percent format issue trick

Excel percent format issue trick. Fix percentage issues. #tutorial #exceltips #excel #excelpro #exceltricks #exceltutorial #exceltraining

12 likes

Excel instructions template

Document instructions by department with an SOP template ready to use. Document procedures and process #tutorial #exceltricks #work #procedure #process

16 likes

Excel vs Word

Excel formatting does the same thing as many people using Word #tutorial #exceltips #exceltricks #exceltutorial #exceltraining #worktips #alwayslearning

4954 likes

Excel drop it function

Remove columns and rows using drop in excel. #exceltips #excel #exceltutorial #businesstips #business #tutorial #boss #spreadsheet

20 likes

An Excel spreadsheet displays a list of full names in column B, with an overlay titled 'HOW TO Separate FIRST & LAST NAME Using the delimiter tool' and a large arrow pointing to the names.

A screenshot of Excel shows the 'Data' tab selected and the 'Text to Columns' wizard open. The first step, 'Delimited', is chosen, and the 'Next' button is highlighted, demonstrating how to begin splitting data.

Two Excel screenshots illustrate the 'Text to Columns' wizard. The top shows selecting 'Space' as the delimiter. The bottom shows the final step, where the destination is set, and the 'Finish' button is highlighted.

HOW TO SEPARATE FIRST & LAST NAMES IN EXCEL

In my daily data management tasks, I often encounter scenarios where first and last names are crammed into a single cell. It can be quite a hassle to manually split them into separate columns. Thankfully, Excel offers a solution that saves me a ton of time: the "Text to Columns" feature.

520 likes

A person wearing glasses and a houndstooth jacket sits in a car, looking at the camera. Text overlay introduces the 'RANDBETWEEN FUNCTION IN EXCEL'.

An Excel screenshot shows the formula bar with '=RANDBETWEEN(1,100)' and cell A1 displaying '12'. Text explains how to generate a random number within a specific range using this Excel formula.

An Excel screenshot shows cell A1 with '12' and the formula '=RANDBETWEEN(1,100)'. Text explains that the number is between 1 and 100 and can be refreshed for a new random value.

HOW I USE RANDBETWEEN IN EXCEL

In the pool of Excel formulas, the RAND() function brings the ability of randomness to my data. However, if you want control over the range of integers generated, enter the RAND() formula in combination with RANDBETWEEN(min, max). With this formula, I can set the minimum and maximum values to tailo

42 likes

Excel dot trick

Do you know the DOT?? Excel trick using the dot to remove extra zeroes #exceltraining #excel #exceltricks #exceltraining #exceltutorial #business #businesstips

106 likes

Excel conditional formatting

HR & People tool practical Excel application of conditional formatting #tutorials #template #excel #exceltricks #exceltraining

9 likes

A collage showcasing pink car interiors, a pink car rear with heart decals, and a car gear shift, with text 'GIRLY cars you'll love SMALL CAR EDiTiON'S'.

A pink Mini Cooper convertible parked in front of a grand, classical building, with 'Mini Cooper' text overlay.

A cream Volkswagen Beetle convertible adorned with a pink bow on the hood, with car keys held above it. Text 'Volkswagen Bettle'.

Car shopping? Here are girly cars to drive🚙

Small car edition: 1. Mini Cooper🩷 (I want it so bad) 2. Volks Bettle 🪲 3. Madza 3&6 🤍 (my personal fav) 4. Honda Civic & Accord 💙🩵 5. Kia K5/ Kia Soul/ Kia Rio 🚘 6. Honda Civic Type R 🏎️ 7. BMW F82 M4 ❄️ 8. Audi 2&3 ☁️ 9. Volks Jetta gli 🖤 Am I missing any other cars that

7269 likes

Excel scannable barcode

Scannable barcode using excel to create them! Download the libre 39 font by just googling it. #tutorial #excel #exceltraining #worktips #elearning #fyp #edu

8 likes

Excel time stamps

Yes, I said permanent time stamps #exceltips #excel #tutorial #worktips #exceltraining #exceltricks

54 likes

A comprehensive cheat sheet for nursing students on drug dosage calculation, including conversions, rounding rules, dimensional analysis, and examples for liquid, injectable, weight-based, solid, and IV flow rate dosages.

A page titled 'Dosage Calculation Examples' from Level Up RN, presenting four worked examples for calculating various medication dosages, including oral tablets, liquid medications, injectable doses, and weight-based doses.

A page displaying multiple-choice questions for nursing students on dosage calculation, specifically focusing on IV infusion rates and completion times, with handwritten calculations for several problems.

Drug dosage

Drug dosage can be tricky for a lot of new nursing students so I tried to show the way I set up my problems & how I get 100% on our drug dosage exams that we take in every class #nursingstudent #nursingjourney #pharmacy #pharmacology #dosagecalc #cheatsheet #learneveryday #Lemo

784 likes

Guys, bookmark this NOW!

As someone who used to think “VLOOKUP” was a yoga pose, this guide saved my sanity. It breaks down EVERYTHING: ✅ Must-know functions (not just =SUM, but =XLOOKUP, =INDEX/MATCH). ✅ Shortcuts that’ll make you 10x faster (Ctrl+Shift+L for filters? Life-changing). ✅ Data validation, conditiona

867 likes

Easiest way to write & memorize numbers 0-10 ✨🥰

How to write your numbers from 0-10!! I had to update the last one I made from 1 year ago because I was missing the 10 & some comments didn’t like the term “heaven” 😭😩 I respect that, so I provided an alternative ✨🫶🏼 now I just need to come up with a song for 11-20 lol *shirt is from my boutiqu

3760 likes

A laptop displays an Excel spreadsheet with charts and data, while a hand interacts with the trackpad. The image features text overlay "Excel shortcuts cheat sheet" and "SWIPE FOR MORE," indicating a guide to Excel shortcuts.

This image lists four essential Excel shortcuts: Autosize Columns (Alt + H + O + I), AutoSum (Alt + =), Display formula in cells (Ctrl + ~), and Insert Current Date (Ctrl + ;). It is part of a multi-page guide.

This image presents four more Excel shortcuts: Insert Hyperlink (Ctrl + K), Freeze Rows & Columns (Alt + W + F + F + F), Fill Down / Fill Right (Ctrl + D and Ctrl + R), and Paste Special - Values (Alt + E, S, N).

Excel shortcuts every student should know! 👩🏻‍💻

Excel shortcuts can significantly improve your efficiency and productivity when working with spreadsheets. Here are some powerful Excel shortcuts that everyone should know. These shortcuts can save you a lot of time and make working with Excel more efficient. Practice using them regularly to become

2720 likes

Count in excel

Count the number of times an item appears in a column or list in excel #tutorial #exceltricks #excel #exceltips #exceltraining #fyp #onthisday

32 likes

Excel signature

Handwritten signature in Excel #tutorials #excel #excelforbeginners #exceltraining #exceltips

12 likes

This image illustrates 20 Excel tips and tricks. It covers adding a dynamic dropdown list, unpivoting data, adding slicer buttons to a PivotTable, using text to columns, transposing data, duplicating windows, and tracking cell values with a watch window. Each tip includes a visual example and brief instructions.

20 Excel Tricks & Tips 👇

Master all 20 and become an excel wizard 🧙‍♂️ 1️⃣ Add a dropdown tied to a dynamic list Know how to create a drop down using data validation? Cool…now how about extending that reference when your list expands? Step 1: create a table out of the list. This will extend the table anytime a

247 likes

Enter 1000 Serial Numbers in ONE Second! 🤯

✨ Welcome to Our Excel Mini-Class! ✨ Hey, Excel pros! 🖥️ Ever needed to quickly fill a column with serial numbers but didn’t want to waste time typing them one by one? 🤔 I’ve got you covered with a super simple trick to enter serial numbers in just ONE second! ⚡ 🔑 Steps to enter serial number

2 likes

Name cells in excel

Customize and store headings in Excel #exceltips #tutorial #exceltraining #exceltricks #exceltutorial #excelforbeginners #learn Takw my Excel class xecutethevision.com#onthisday

32 likes

$An infographic details Excel Custom Formatting, covering its definition, access methods, and syntax for positive, negative, and zero values. It illustrates various operators like numbers, colors, and symbols, alongside examples for formatting dates, conditional formatting, fractions, scientific notation, percentages, custom text, number abbreviations, and using symbols.$