At most one email a week. Unfollow anytime with one click.
Transcript
The full narration of this video. The current sentence is highlighted while the video plays; click any sentence to jump there.
Show transcript
Introduction
This is the final result we will create, the inventory table for stationery items. Items with stock below the minimum threshold are automatically colored red. The stock difference is completed with an arrow icon, and the category column has a drop-down selection list. And the stock column rejects letters or numbers that don't make sense. Let's start. Hello, welcome to the Excel tutorial for beginners, conditional formatting and data validation. We will make Excel color cells on its own when the stock of items is running low. Display an arrow icon, and create a drop-down selection list and input restrictions to prevent data entry errors.
Step 1: Warehouse stationery stock data
This is the inventory table for stationery items, code, item name, category, stock, and minimum stock. We want to quickly know which items have stock already below the minimum threshold. Reading numbers one by one is tiring. It's better to let Excel highlight them with color. That is the function of conditional formatting. First, select the stock column, from row 2 to row 11.
Step 2: Highlight stock below minimum
Open the Home tab, click Conditional Formatting, then Highlight Cell Rules, and select Less Than. In the box that appears, type equal to, dollar sign E, number 2. This means, highlight stock that is less than the minimum stock in the same row. The default color is light red with dark red text. Click OK.
Step 3: Color changes when stock changes
Look, items with stock below the minimum are now colored red. Pens, markers, notebooks, and scissors need to be purchased soon. The color is vivid. If the stock is changed, the color also changes. Let's try changing the stock of pens to 100, and the red color disappears.
Step 4: Difference column with arrow icon
We will add a column for the difference. In cell F1, type 'difference', in F2 type 'equal to D2 minus E2', then drag down. Select F2 to F11, open Conditional Formatting, select Icon Sets, then choose 3 colored arrows. Green arrow for large difference, yellow for medium, and red for small. Now, data validation.
Step 5: Dropdown selection list
We will add a dropdown list for the category column. Select C2 to C11, open the Data tab, then click Data Validation. In Allow, select List. In Source, type paper, semicolon, writing tool, semicolon, supplies. Click OK.
Step 6: Selecting category from dropdown
Click cell C2. A small arrow appears next to the cell. Click the arrow and select a category from the list. With the dropdown, everyone fills in the category uniformly, without typos. We fill in the category for all items.
Step 7: Limiting input to numbers
Finally, set input restrictions for numbers. Select D2 to D11. Open Data Validation again, in Allow select Number, and set between 0 and 1000. Now try typing letters ABC in cell D2. Excel rejects it and displays an error message. This prevents incorrect data from entering from the start.
Step 8: Clean up and save
Adjust column widths and save with Ctrl + S. With conditional formatting and data validation, our table is more informative and safer from errors. Combine both methods. And anyone filling out this file will be helped. Color indicates when to buy an item, drop-down keeps the category consistent, and number limits reject unreasonable data. This is the basis for creating an Excel file that can be used safely with others.
Done! The stock table is now smart and secure
Done. We have learned conditional formatting with colors and icons, dropdown selection lists, and input restrictions with data validation. Thank you for watching. See you in other tutorial series. Want to master Excel for work and business? Cerdas Inovasi Digital offers Excel and Office courses, template creation services, reports, and Excel dashboards, as well as selling this tutorial practice file. Contact us via WhatsApp, or visit cerdasinovasidigital.com Thank you for watching. See you in other tutorial series.
Ask the class material
Type a question or keyword; we find the part of this episode that explains it and jump the video there.
My notes
Notes are saved with the current video time and are only visible to you.
Questions & answers
Stuck on a step? Ask here; the instructor and other learners can answer.
No questions yet.
Your instructor
Cerdas Inovasi Digital Team
Software developers, BIM & 3D engineers, and AI practitioners
53 video classes1,279 episodes
We build software, BIM models, games and AI automations for clients every day, and turn that hands-on work into step-by-step classes: every step is shown on screen and comes with a PDF guide you can keep.
