{"id":18753,"date":"2026-04-26T08:00:00","date_gmt":"2026-04-26T01:00:00","guid":{"rendered":"https:\/\/beup.space\/?p=18753"},"modified":"2026-05-02T16:22:55","modified_gmt":"2026-05-02T09:22:55","slug":"thiet-ke-file-excel-tu-duy-he-thong","status":"publish","type":"post","link":"https:\/\/beup.space\/en\/thiet-ke-file-excel-tu-duy-he-thong\/","title":{"rendered":"Designing Excel files \u2014 why even the most skilled Excel users create the most cluttered files"},"content":{"rendered":"

\u23f1 18-minute read<\/p>\n

<\/p>\n

\n

Short answer:<\/strong> Properly designing an Excel file means separating it into three non-mixing layers \u2014 DATA<\/strong> INPUT (where data is entered), LOGIC<\/strong> LOGIC (where data is processed), OUTPUT<\/strong> OUTPUT (where results are read) \u2014 along with the principle of a single source of truth for all changeable parameters. Most cluttered Excel files aren’t due to weak formulas but because these three layers are mixed into one sheet, leading to a situation where, after a few months, no one \u2014 including the file creator \u2014 understands it anymore.<\/p>\n<\/div>\n

<\/p>\n

\n \"S\u01a1
The 3-layer architecture of a well-designed Excel file \u2014 the foundation that all stable files follow<\/figcaption><\/figure>\n

Many people learn Excel by searching for formulas for specific problems. VLOOKUP, SUMIF, IF nested in IF \u2014 they learn these but still create cluttered files, spend hours finding errors, and are afraid to let others use them because they fear the file will be “broken”. The problem isn’t Excel. The problem is that formulas are just tools, and what they haven’t learned is design<\/em> designing the file before starting work<\/p>\n

Compare two people who learn Excel for three months. Person A opens Excel and starts entering data immediately, thinking on the fly, adding new columns as needed, coloring for aesthetics, and copying formulas from Google. Person B spends fifteen minutes sketching on paper: what the input data is, what reports are needed, who will use the file, how many sheets, and what each sheet does \u2014 before opening Excel. After three months, Person A’s file is incomprehensible, even to themselves. Person B’s file still runs smoothly, can be expanded, and a new user can learn it in thirty minutes. The difference lies entirely in systems thinking.<\/p>\n

Designing an Excel file is like designing a house. You can’t build walls before having a blueprint. You can’t cram bedrooms, kitchens, and living rooms into one room. Each area must have a clear function and not overlap. Electrical and water lines must be routed correctly \u2014 not running wildly through walls.<\/p>\n

\n

Core principle:<\/strong> Data enters one place. Logic is processed in one place. Results are displayed in one place. These three layers never mix.<\/p>\n<\/div>\n

\n

Article contents<\/p>\n

    \n
  1. The three layers of a well-designed Excel file<\/a><\/li>\n
  2. Single Source of Truth \u2014 one source for all data<\/a><\/li>\n
  3. Flat Data Structure \u2014 organized data<\/a><\/li>\n
  4. Excel Table \u2014 the most powerful overlooked feature<\/a><\/li>\n
  5. Named Ranges \u2014 naming data ranges<\/a><\/li>\n
  6. Systematic colors \u2014 status language<\/a><\/li>\n
  7. Data Validation \u2014 protecting files from user errors<\/a><\/li>\n
  8. Self-explanatory formulas<\/a><\/li>\n
  9. Two files, two results \u2014 a true story<\/a><\/li>\n
  10. Checklist of 15 questions to ask before opening Excel<\/a><\/li>\n<\/ol>\n<\/div>\n
    \n

    The three layers of a well-designed Excel file<\/h2>\n

    This is the foundation of everything. Any Excel file \u2014 whether simple or complex \u2014 should be built on these three layers. When these layers are mixed, no formula is smart enough to save the file from becoming disorganized after six months.<\/p>\n

    Level 1 \u2014 DATA (data source)<\/h3>\n

    DATA is where you enter raw data. No flashy colors, no complex formulas, no merged cells. Just clean, consistent, raw data. The right mindset: your DATA sheet is your data foundation — it doesn't need to be visually appealing, it needs to be accurate and consistent.<\/p>\n

    Practical example for an F&B store managing sales:<\/p>\n\n\n\n\n\n\n\n\n
    Ng\u00e0y<\/th>\nProduct code<\/th>\nDish name<\/th>\nQuantity<\/th>\nUnit<\/th>\nSelling price<\/th>\nSales channel<\/th>\n<\/tr>\n<\/thead>\n
    01\/04\/2025<\/td>\nPHO-01<\/td>\nRare beef noodle soup<\/td>\n50<\/td>\nt\u00f4<\/td>\n65,000<\/td>\nAt the restaurant<\/td>\n<\/tr>\n
    01\/04\/2025<\/td>\nBUN-02<\/td>\nHue beef noodle soup<\/td>\n30<\/td>\nt\u00f4<\/td>\n55,000<\/td>\nGrabFood<\/td>\n<\/tr>\n
    01\/04\/2025<\/td>\nPHO-01<\/td>\nRare beef noodle soup<\/td>\n12<\/td>\nt\u00f4<\/td>\n65,000<\/td>\nShopeeFood<\/td>\n<\/tr>\n
    02\/04\/2025<\/td>\nCOM-05<\/td>\nBroken rice with grilled pork<\/td>\n45<\/td>\nph\u1ea7n<\/td>\n45,000<\/td>\nAt the restaurant<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    Seven golden principles for the DATA sheet<\/strong>, applicable to all types of files without exception:<\/p>\n

      \n
    1. One row is one transaction or one event<\/strong> \u2014 never combine multiple things into one row<\/li>\n
    2. Do not merge cells<\/strong> \u2014 merging cells breaks all formulas, all Pivot Tables, and all filters<\/li>\n
    3. Do not leave empty columns between columns<\/strong> \u2014 Excel will not recognize a continuous data range<\/li>\n
    4. Headers are in row 1, data starts from row 2<\/strong> \u2014 never have secondary titles in between<\/li>\n
    5. Do not color based on emotions<\/strong> \u2014 color in DATA is only used for Conditional Formatting with logic<\/li>\n
    6. Do not embed calculation formulas in DATA<\/strong> \u2014 DATA only contains original values<\/li>\n
    7. Clear column names, no unclear abbreviations<\/strong> \u2014 “DT” is far inferior to “Doanh_Thu”<\/li>\n<\/ol>\n

      Common errors to avoid<\/strong>: adding a secondary header row like “=== April ===” between data to separate by month is structurally incorrect \u2014 instead, add a “Month” or “Quarter” column and use filters\/groups to separate. Merging cells like “Beef Pho” for three identical orders is also structurally incorrect \u2014 repeat “Beef Pho” in each row; flat data with each row independent is a requirement for Pivot Tables to work.<\/p>\n

      Layer 2 \u2014 LOGIC (processing and calculation)<\/h3>\n

      LOGIC is the “factory” of the file \u2014 where all processing takes place. End-users typically don't need to look at this. Sheets in this layer are often hidden or named with special characters to distinguish them. Three standard sheet types in the LOGIC layer:<\/p>\n

      Sheet _CONFIG<\/code> \u2014 system configuration and parameters.<\/strong> Contains all fixed numbers: exchange rates, tax rates, service fees, catalog lists (products, employees, sales channels), warning thresholds (minimum inventory, sales targets), and company information (name, address, tax code) for reporting. Rule: any number that can change over time \u2014 must be in _CONFIG<\/code>, never hard-code into formulas.<\/p>\n

      Sheet _CALC<\/code> \u2014 intermediate calculation tables.<\/strong> Contains complex calculations that don't need to be displayed directly: cost calculations based on recipes (for F&B), cost allocation tables by period, and SUMIF\/SUMPRODUCT aggregations from previous DATA before being displayed on the dashboard. For example, F&B: _CALC<\/code> Contains recipe tables with ingredient ratios and calculates the cost of each dish; the dashboard simply references the results from here.<\/p>\n

      Sheet _REF<\/code> \u2014 reference tables and catalogs.<\/strong> Contains lookup tables: product code tables with full category names and selling prices, employee tables with departments and positions, province tables with economic regions and shipping fees. When there are tables _REF<\/code> standard, all VLOOKUP\/XLOOKUP formulas reference this sheet \u2014 never the DATA sheet.<\/p>\n

      Sheet naming convention in the LOGIC layer: starts with an underscore to automatically group and easily recognize, or uses lowercase (config<\/code>, calc<\/code>, ref<\/code>) distinguish from uppercase for OUTPUT and DATA. Shade the LOGIC tab sheet gray to differentiate it from DATA (white) and OUTPUT (blue).<\/p>\n

      Layer 3 \u2014 OUTPUT (results and dashboard)<\/h3>\n

      OUTPUT is where users look to make decisions. Information is filtered, summarized, and visualized. No cells are manually entered here \u2014 all are formulas referencing LOGIC or DATA. Five principles for good OUTPUT design: place the most important information in the top-left corner (people read from left to right, top to bottom); group related information together, avoiding scattered data; use color and icons purposefully, not for decoration; each figure must have context (revenue of 50 million is meaningless without comparison); avoid too many numbers, selecting 5-7 key KPIs instead of dumping all data.<\/p>\n

      Monthly Dashboard for an F&B establishment \u2014 what needs to be displayed:<\/p>\n\n\n\n\n\n\n\n\n\n
      KPI<\/th>\nThis month<\/th>\nPrevious month<\/th>\nTarget<\/th>\nStatus<\/th>\n<\/tr>\n<\/thead>\n
      Revenue<\/td>\n52,400,000<\/td>\n48,200,000<\/td>\n50,000,000<\/td>\n\ud83d\udfe2 +8.7% vs target<\/td>\n<\/tr>\n
      Cost of goods sold (COGS)<\/td>\n21,800,000<\/td>\n19,500,000<\/td>\n\u2264 22,000,000<\/td>\n\ud83d\udfe2 41.6% — stable<\/td>\n<\/tr>\n
      Gross profit<\/td>\n30,600,000<\/td>\n28,700,000<\/td>\n28,000,000<\/td>\n\ud83d\udfe2 +6.6%<\/td>\n<\/tr>\n
      Number of transactions<\/td>\n1,240<\/td>\n1,180<\/td>\n1,200<\/td>\n\ud83d\udfe2 Exceeding target<\/td>\n<\/tr>\n
      Inventory below threshold<\/td>\n3 items<\/td>\n1 item<\/td>\n0<\/td>\n\ud83d\udd34 Needs additional input<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

      The “Status” column is created using Conditional Formatting combined with IF formulas — fully automated, no manual input. The owner opens the file in the morning and can see what needs to be done in just three seconds by looking at the dashboard.<\/p>\n

      \n

      Single Source of Truth \u2014 one source for all data<\/h2>\n

      This is the most important principle and also the most frequently violated one. A typical error seen in most SME files: the cost of a dish is manually entered in the inventory sheet, entered again in the sales sheet, and entered once more in the monthly report sheet. Three months later, the three places have three different numbers \u2014 and no one knows which one is correct.<\/p>\n

      Or more commonly: the 8% VAT rate is hardcoded into 47 different formula cells. When the government changes the tax rate, the file owner has to search for and update each cell individually \u2014 and will likely miss a few places.<\/p>\n

      The correct rule<\/strong>any information that may need to be changed or used in multiple places \u2014 must have a single original cell in the _CONFIG<\/code>All other places can only reference that cell using a formula.<\/p>\n

      A real-life example for exchange rates and tax rates:<\/p>\n

      Sheet _CONFIG:\nB2 = 25,400        \u2190 USD\/VND exchange rate \u2014 ONLY UPDATE HERE\nB3 = 8%            \u2190 VAT \u2014 ONLY UPDATE HERE\nB4 = 3.5%          \u2190 GrabFood fee \u2014 ONLY UPDATE HERE\nB5 = 2.2%          \u2190 ShopeeFood fee \u2014 ONLY UPDATE HERE\n\nSales sheet (formula):\n= C5 * _CONFIG!$B$2          \u2190 Convert USD to VND\n= D5 * (1 + _CONFIG!$B$3)    \u2190 Add VAT\n= E5 * (1 - _CONFIG!$B$4)    \u2190 Subtract Grab fee<\/code><\/pre>\n
      When Grab increases its fee from 3.5% to 4% \u2014 you only need to update one cell (_CONFIG!B4<\/code>), the entire file updates in a few seconds.<\/p>\n
      This principle also applies to product categories. Instead of entering product names directly into each row in the DATA sheet, create a category table in _REF<\/code> \u2014 the DATA sheet only enters the product code, while the name, price, and category are all looked up from the REF table. When the selling price changes, you only need to update one cell in _REF<\/code>the entire sales history remains correct (since sales are taken from the entered unit price column), but the new price analysis reports update automatically.<\/p>\n
      \n
      Single Source of Truth<\/strong> is the principle that each piece of data should be stored in only one place. Everywhere else that needs that data should reference it, not copy the value.<\/p>\n<\/div>\n
      \n
      Flat Data Structure \u2014 organized data<\/h2>\n
      This concept is often unknown to self-taught Excel users — and it makes a huge difference between an analyzable file and one that's just for viewing. Excel is designed to work with tabular data. All of Excel's powerful tools — SUMIF, COUNTIFS, Pivot Table, charts, Power Query — work best with flat data.<\/p>\n
      The wrong approach \u2014 data in wide format<\/strong>Many people create employee sales reports in this way:<\/p>\n\n\n\n\n\n\n\n
      Employee<\/th>\nJanuary<\/th>\nFebruary<\/th>\nMarch<\/th>\nApril<\/th>\nMay<\/th>\nJune<\/th>\n<\/tr>\n<\/thead>\n
      Nguy\u1ec5n An<\/td>\n45M<\/td>\n52M<\/td>\n48M<\/td>\n61M<\/td>\n55M<\/td>\n58M<\/td>\n<\/tr>\n
      Tr\u1ea7n B\u00ecnh<\/td>\n38M<\/td>\n41M<\/td>\n44M<\/td>\n39M<\/td>\n47M<\/td>\n52M<\/td>\n<\/tr>\n
      L\u00ea Ch\u00e2u<\/td>\n29M<\/td>\n33M<\/td>\n31M<\/td>\n35M<\/td>\n42M<\/td>\n38M<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      This method looks “compact” but is extremely limited. Want to calculate the total Q1 sales for the entire team? You have to do it manually. Want to filter to see which month has an employee with sales under 40M? Can't do it. Adding a new month? You have to add a column, and all aggregate formulas need to be revised. Pivot Table? Almost impossible to use.<\/p>\n
      The correct way \u2014 flat data (long\/flat format)<\/strong>:<\/p>\n\n\n\n\n\n\n\n\n
      Employee<\/th>\nMonth<\/th>\nN\u0103m<\/th>\nSales<\/th>\nV\u00f9ng<\/th>\n<\/tr>\n<\/thead>\n
      Nguy\u1ec5n An<\/td>\n1<\/td>\n2025<\/td>\n45,000,000<\/td>\nSouthern region<\/td>\n<\/tr>\n
      Nguy\u1ec5n An<\/td>\n2<\/td>\n2025<\/td>\n52,000,000<\/td>\nSouthern region<\/td>\n<\/tr>\n
      Tr\u1ea7n B\u00ecnh<\/td>\n1<\/td>\n2025<\/td>\n38,000,000<\/td>\nNorthern region<\/td>\n<\/tr>\n
      Tr\u1ea7n B\u00ecnh<\/td>\n2<\/td>\n2025<\/td>\n41,000,000<\/td>\nNorthern region<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      Now you can do it all:<\/p>\n
      ' Total Q1 sales for the entire team:\n=SUMPRODUCT((DOANH_SO[Year]=2025)*(DOANH_SO[Month]<=3)*DOANH_SO[Sales])\n\n' Average monthly sales of Nguy\u1ec5n An:\n=AVERAGEIF(DOANH_SO[Employee],\"Nguy\u1ec5n An\",DOANH_SO[Sales])\n\n' Pivot Table: drag and drop for 30 seconds to get a report by region \u00d7 month<\/code><\/pre>\n
      And most importantly: adding new monthly data is just adding a row, without modifying any formulas.<\/p>\n
      Summary:<\/strong> Data in “long” format is always easier to analyze than data in “wide” format. If your file is in a wide format, that's a clear sign that Pivot Table and automatic reports will never work.<\/p>\n
      \n
      Excel Table \u2014 the most powerful overlooked feature<\/h2>\n
      Many Excel users, even after a decade, have never used Excel Table (Ctrl+T). This is one of the most powerful and least known features \u2014 a must-have for a sustainable file.<\/p>\n
      Excel Table automatically does what you're doing manually: automatically expands the data range when adding new rows (SUMIF formula doesn't need to update the range); column names replace cell addresses in formulas (=[@Doanh_so] * [@Gia_von]<\/code> easier to read =D2*E2<\/code>); automatic filter is added to the header; alternating format (banded rows) makes it easier to read and is automatically applied to new rows; total row (Total Row) with dropdown selection of SUM, AVERAGE, COUNT, MAX, MIN.<\/p>\n
      How to create<\/strong>: click on any cell in the data range \u2192 Ctrl+T \u2192 OK. Then, rename the Table instead of “Table1”: select the tab Table Design<\/em>; enter the name in the box Table Name<\/em> (for example SALES<\/code>, INVENTORY<\/code>, EMPLOYEE<\/code>).<\/p>\n
      After naming, the formula becomes self-explanatory:<\/p>\n
      Instead of:\n=SUMIF($A$2:$A$1000, \"Grab\", $D$2:$D$1000)\n\nWrite as:\n=SUMIF(SALES[Sales Channel], \"Grab\", SALES[Sales])<\/code><\/pre>\n
      The second formula is easy to understand for anyone: calculate the total Sales<\/code> from the table SALES<\/code>filter by Sales Channel<\/code> using “Grab”. No need to open another sheet to decode cell addresses.<\/p>\n
      \n
      Named Ranges \u2014 naming data ranges<\/h2>\n
      In addition to Excel Table, you should combine Named Ranges for important cells or reference ranges. When to use Named Ranges instead of (or in combination with) Excel Table? For single cells like exchange rates, tax rates, warning thresholds; for fixed ranges like category tables, price tables; when you need to reference cross-sheet easily.<\/p>\n
      Naming Method<\/strong>: select a cell or range \u2192 click on the Name Box (cell address display, top left corner) \u2192 type a name \u2192 Enter. Or use Formulas \u2192 Name Manager<\/em> for centralized management.<\/p>\n
      Good naming convention:<\/p>\n\n\n\n\n\n\n\n
      Lo\u1ea1i<\/th>\nExample Name<\/th>\nMeaning<\/th>\n<\/tr>\n<\/thead>\n
      Single Parameter<\/td>\nUSD Exchange Rate<\/code>, VAT<\/code>, TARGET_LIST<\/code><\/td>\nSingle Cell _CONFIG<\/code><\/td>\n<\/tr>\n
      List<\/td>\nProduct List<\/code>, DS_KENH<\/code>, DS_NHAN_VIEN<\/code><\/td>\nDropdown validation area<\/td>\n<\/tr>\n
      Reference table<\/td>\nBANG_GIA<\/code>, BANG_GIA_VON<\/code>, BANG_CUOC_VAN<\/code><\/td>\nVLOOKUP\/XLOOKUP area<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      Actual results when combining Excel Tables with Named Ranges:<\/p>\n
      Instead of:\n=VLOOKUP(A2, _REF!$A$2:$F$200, 5, 0) * Sheet3!$B$2\n\nWrite:\n=XLOOKUP([@Ma_SP], BANG_GIA[Ma_SP], BANG_GIA[Gia_ban]) * TY_GIA_USD<\/code><\/pre>\n
      When updating exchange rates or price lists, make changes in the right place without altering formulas.<\/p>\n
      \n
      Systematic colors \u2014 status language<\/h2>\n
      Colors in Excel are not just for aesthetics. Colors are a visual language that conveys information about the structure and status of a file. Standard color system \u2014 applied consistently across all files:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n
      Background color<\/th>\nMeaning<\/th>\nCell type<\/th>\nUser capabilities<\/th>\n<\/tr>\n<\/thead>\n
       Light yellow (#FFF2CC)<\/td>\nData entry<\/td>\nInput cell<\/td>\nFreely editable<\/td>\n<\/tr>\n
       Light blue (#DEEAF1)<\/td>\nAuto formula<\/td>\nFormula cell<\/td>\nNot manually editable<\/td>\n<\/tr>\n
      ↵ White \/ very light gray<\/td>\nRaw data<\/td>\nData cell<\/td>\nEnter according to regulations<\/td>\n<\/tr>\n
      \ud83d\udfeb Dark gray + white text<\/td>\nHeader<\/td>\nColumn header<\/td>\nDo not edit<\/td>\n<\/tr>\n
      \ud83d\udfe2 Light green (#E2EFDA)<\/td>\nGood result \/ meets target<\/td>\nConditional<\/td>\nAutomatic based on logic<\/td>\n<\/tr>\n
      \ud83d\udd34 Light red (#F8CBAD)<\/td>\nWarning \/ below threshold<\/td>\nConditional<\/td>\nAutomatic based on logic<\/td>\n<\/tr>\n
      \ud83d\udfe0 Light orange (#FCE4D6)<\/td>\nNear threshold \/ needs attention<\/td>\nConditional<\/td>\nAutomatic based on logic<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      The first golden rule<\/strong>: anyone looking at your file for the first time should immediately understand which cells are input and which are results \u2014 just by color, without explanation. The second golden rule<\/strong>: sheet tabs should also be colored \u2014 white or default for DATA, gray for LOGIC (_CONFIG<\/code>, _CALC<\/code>, _REF<\/code>), green for OUTPUT\/Dashboard, red for temporary or pending sheets.<\/p>\n
      Practical Conditional Formatting \u2014 inventory example:<\/p>\n
      Column G = Current inventory\nColumn H = Minimum inventory level (from _CONFIG)\n\nRule 1: If G < H       \u2192 red background + bold red text (\"Need to order\")\nRule 2: If G < H*1.2   \u2192 orange background (\"Running low\")\nRule 3: If G >= H*2    \u2192 green background (\"In stock\")<\/code><\/pre>\n
      No need to look at the numbers, just look at the color to know what to do.<\/p>\n
      \n
      Data Validation \u2014 protecting files from user errors<\/h2>\n
      The best Excel file is one that cannot be entered incorrectly \u2014 or if entered incorrectly, it warns immediately. Core principle: design for failure<\/em>Assuming the user s\u1ebd<\/em> Incorrect input, and designing to prevent it from the start. Four types of validation you should know:<\/p>\n
      Type 1 \u2014 Dropdown List (most common)<\/h3>\n
      In a real-world order management file, the name \"Grab Food\" had seven different spellings in the same column: \"Grab\", \"GrabFood\", \"grab food\", \"GRAB\", \"Grab Food\", \"grab\", \"GrabFoods\". Result: sales channel reports were completely incorrect. A dropdown list solves this problem in two minutes.<\/p>\n
      Step 1: Create a list in _REF (e.g., DS_KENH_BAN)\n    In-store\n    GrabFood\n    ShopeeFood\n    BeFood\n    Baemin\n\nStep 2: Select the sales channel column in the DATA sheet\nStep 3: Data \u2192 Data Validation \u2192 Allow: List\nStep 4: Source: =DS_KENH_BAN\nStep 5: Error Alert \u2192 \"Invalid sales channel. Please select from the list.\"<\/code><\/pre>\n
      Users can only select from the dropdown \u2014 cannot enter incorrectly. Advanced tip: dependent dropdown<\/strong> (dependent dropdown) \u2014 select a category and the product dropdown only shows products belonging to that category; use the INDIRECT<\/code> Combine Named Ranges with category-based naming.<\/p>\n
      Type 2 \u2014 Data type restriction<\/h3>\n
      Quantity column: Allow = Whole number, Minimum = 1\nSelling price column:  Allow = Decimal, Minimum = 0\nDate column:     Allow = Date, limited from the opening date to today<\/code><\/pre>\n
      Type 3 \u2014 Custom Formula (advanced)<\/h3>\n
      Ensure the product code always starts with uppercase letters:<\/p>\n
      =AND(LEN(A2)=6, EXACT(LEFT(A2,3), UPPER(LEFT(A2,3))))<\/code><\/pre>\n
      Prevent duplicate entries in the employee code column:<\/p>\n
      =COUNTIF($A$2:$A$1000, A2) = 1<\/code><\/pre>\n
      Type 4 \u2014 Protect Sheet<\/h3>\n
      After designing, lock the entire sheet and only allow input in designated cells:<\/p>\n
      Step 1: Ctrl+A \u2192 Format Cells \u2192 Protection \u2192 uncheck \"Locked\"\nStep 2: Select INPUT cells only \u2192 recheck \"Locked\"\n         (Reverse logic: default is unlocked, only lock cells that need protection)\nStep 3: Review \u2192 Protect Sheet \u2192 set a password<\/code><\/pre>\n
      Result: users can only click on the yellow cell (INPUT). All formula cells are automatically protected and cannot be broken, even accidentally.<\/p>\n
      \n
      Self-explanatory formula \u2014 Formula Readability<\/h2>\n
      A good formula is not only accurate \u2014 it must be easy to read, verify, and modify. Three techniques completely change the quality of a formula:<\/p>\n
      IFS instead of nested IFs<\/strong>Four-level nested IF is nearly impossible to verify visually:<\/p>\n
      \u274c Hard to read (4-level nested IF):\n=IF(D2>50000000,\"Excellent\",IF(D2>40000000,\"Good\",IF(D2>30000000,\"Fair\",IF(D2>20000000,\"Needs improvement\",\"Fail\"))))\n\n\u2705 Easier to read with IFS (Excel 2019+):\n=IFS(\n    D2 > 50000000, \"Excellent\",\n    D2 > 40000000, \"Good\",\n    D2 > 30000000, \"Fair\",\n    D2 > 20000000, \"Needs improvement\",\n    TRUE,          \"Fail\"\n)<\/code><\/pre>\n
      Replace VLOOKUP with XLOOKUP<\/strong>VLOOKUP has clear limitations and is very prone to errors when adding columns to the table:<\/p>\n
      VLOOKUP \u2014 limited and error-prone:\n=VLOOKUP(A2, BANG_GIA, 4, 0)\n                      \u2191 What is column 4? Must count manually. Adding a column in between \u2192 the number 4 is incorrect.\n\nXLOOKUP \u2014 explicit and flexible:\n=XLOOKUP(A2, BANG_GIA[Ma_SP], BANG_GIA[Gia_ban], \"Not found\")\n           \u2191 Where to find A2?  \u2191 return which column?     \u2191 what to return if not found?<\/code><\/pre>\n
      Break down complex formulas<\/strong>Instead of writing a 200-character formula, calculate each step in auxiliary columns (which can be hidden later):<\/p>\n
      Column H (hidden) = Gross Revenue: =[@SL] * [@Gia_ban]\nColumn I (hidden) = Platform Fee: =[@DT_tho] * XLOOKUP([@Kenh], BANG_PHI[Kenh], BANG_PHI[Phi])\nColumn J (hidden) = VAT: =[@DT_tho] * THUE_VAT\nColumn K = Net Revenue: =[@DT_tho] - [@Phi_platform] - [@Thue_VAT]<\/code><\/pre>\n
      Now, each step can be checked separately \u2014 10 times easier to debug than a monolithic formula.<\/p>\n
      \n
      Two files, two results \u2014 a true story<\/h2>\n
      I once received two files from two restaurant owners of the same scale and type, with annual revenues of around 2 billion. However, their management files represented two different worlds.<\/p>\n
      File A \u2014 the \"emotional file\"<\/strong>A single sheet with 47 columns, hundreds of merged cells, and random color fills. Formulas are copied from Google, with half of them returning #REF when adding new rows. Cost is entered manually in three places \u2014 with three different numbers. The owner spends two hours per week updating and is still unsure if the data is correct. When new employees join, the owner must spend hours explaining and still monitors them for fear of \"breaking the file\".<\/p>\n
      File B \u2014 the \"design file\"<\/strong>Five sheets: DATA, _CONFIG<\/code>, _CALC<\/code>, _REF<\/code>the Dashboard. Consistent color scheme \u2014 yellow for input, green for formulas. All categories are dropdowns from _REF<\/code>Cost and other parameters are centralized in _CONFIG<\/code>The dashboard updates automatically when new rows are added to the DATA sheet. The owner spends 15 minutes per week, the data is always accurate, and new employees can use it after 30 minutes without guidance.<\/p>\n
      File B is not more complex than File A in terms of Excel skills \u2014 in fact, it has fewer formulas. The difference lies entirely in the design thinking before opening Excel.<\/p>\n
      \n
      Checklist of 15 questions to ask before opening Excel<\/h2>\n
      Spending 15 minutes sketching on paper can save dozens of hours of troubleshooting later. Before pressing Ctrl+N, answer these 15 questions on paper, not in your head, divided into three phases:<\/p>\n
      Problem definition phase:<\/strong><\/p>\n
        \n
      1. What specific problem does this file solve?<\/li>\n
      2. Who will use this file, and what is their Excel skill level?<\/li>\n
      3. What is the usage frequency: daily, weekly, or monthly?<\/li>\n
      4. How long will the file be used \u2014 one month or three years?<\/li>\n<\/ol>\n
        Data design phase:<\/strong><\/p>\n
          \n
        1. What are the input data sources, and who will enter them?<\/li>\n
        2. What are the potential errors in data entry? What validation is needed?<\/li>\n
        3. How many sheets are needed? What are their names?<\/li>\n
        4. What output is needed — numerical table, chart, or dashboard?<\/li>\n<\/ol>\n
          Construction phase:<\/strong><\/p>\n
            \n
          1. Separate DATA \/ LOGIC \/ OUTPUT — no mixing<\/li>\n
          2. T\u1ea1o _CONFIG<\/code> for all adjustable parameters<\/li>\n
          3. T\u1ea1o _REF<\/code> for all categories and lookup tables<\/li>\n
          4. Define colors from the start and document them at the beginning of the file<\/li>\n
          5. Name all data ranges as Excel Tables<\/li>\n
          6. Lock formula cells, unlock only input cells<\/li>\n
          7. Test: ask someone else to try it for ten minutes without instructions<\/li>\n<\/ol>\n
            If the fifteen-minute test fails, go back and redesign. Don\u2019t build on a weak foundation.<\/p>\n
            \n
            In short \u2014 Right Thinking First<\/h2>\n
            Excel isn\u2019t difficult. The difficulty lies in systems thinking before opening the file. Everything in this article boils down to one philosophy: design first, build later, never do it the other way around<\/strong>.<\/p>\n
            A good Excel file isn\u2019t the one with the most formulas, nor the one that looks the prettiest. A good Excel file is one that anyone on the team can understand and use after reading a short guide; data is always consistent, with no conflicting numbers; it\u2019s easy to maintain after six months, even if you don\u2019t remember what you did; it\u2019s difficult to enter incorrect data, thanks to validation and color-coded guidance; and it automatically updates when new data is added.<\/p>\n
            Next time before pressing Ctrl+N, take a piece of paper and sketch out: what does your DATA sheet contain? Sheet _CONFIG<\/code> What parameters are needed? What KPIs should the dashboard display? Fifteen minutes sketching on paper will save you dozens of hours of debugging later. When you can answer those questions before opening Excel \u2014 you\u2019re on the right track.<\/p>\n
            \n
            \u270d Key Takeaways<\/p>\n