Blog

When working with large sets of data in Microsoft Excel, it becomes difficult to find or analyze specific information manually. Sorting in Excel means arranging your data in a specific order — either alphabetically, numerically, or by date. For example, if you have a list of students with their marks, you can sort them: From highest to lowest marks, or Alphabetically by student name   Types of Data Sorting in Excel There are mainly two ways to sort data in MS Excel: This sorts your data from the lowest to highest value. Example: Name Marks Rahul 95 Ankit 80 Sneha 90 After Ascending Sort (Smallest to Largest): Name Marks Ankit 80 Sneha 90 Rahul 95   This sorts data from highest to lowest value. Example: Using the same table above, after Descending Sort, Excel will display: Name Marks Rahul 95 Sneha 90 Ankit 80   Advanced Sorting Options You can also perform multi-level sorting: Sort first by Department Then by Employee Name Finally by Salary This gives better control when working with large datasets.   Benefits of Sorting in Excel 1 Helps organize data neatly 2 Makes analysis faster 3 Highlights top or bottom performers 4 Useful in reports, attendance lists, and sales records   What is Filter in MS Excel? Filtering in Excel helps you display only the data you need while hiding the rest temporarily. It doesn’t delete any information — it just hides rows that don’t match your selected criteria. When you enable a filter, small drop-down arrows appear beside each column header. You can use them to select, deselect, or search for specific data.   Example of Using Filter in Excel Product Category Price Laptop Electronics 50000 Chair Furniture 3000 Mouse Electronics 500 Table Furniture 2500   Goal: You want to show only Electronics items. Steps: Select your data range Go to Home → Sort & Filter → Filter Click the drop-down arrow in the Category column Uncheck all, then select Electronics Click OK Result: Only rows with “Electronics” appear on screen — Laptop and Mouse.   Types of Filters in Excel Text Filter Used for text-based columns. Options include: Equals Does Not Equal Begins With Ends With Contains Example: Filter all names starting with “A”.   Number Filter Used for numeric columns. Options include: Greater Than Less Than Between Top 10 Example: Show only products with prices greater than ₹1000.   Date Filter Used for columns with date entries. Options include: Before / After a specific date This Month / Last Month Between two dates Example: Filter all sales entries for September 2025 only.   Advanced Filtering You can use the Advanced Filter (from the Data Tab) for complex conditions — such as filtering by multiple criteria or copying results to another location.   Difference Between Sorting and Filtering Feature Sorting Filtering Purpose Arranges data in order Displays selected data only Effect Reorders all rows Hides unwanted rows Use Case Ranking students by marks Viewing only Electronics products Data Loss No No (hidden, not deleted)   Conclusion Both Sorting and Filtering are essential tools for managing and analyzing data in Excel. While sorting helps you arrange information systematically, filtering helps you focus only on the relevant data.  

A Table in MS Excel is a structured range of data that allows you to easily manage and analyze information.When you convert your data into a table, Excel automatically applies formatting, filter buttons, and built-in tools for sorting and analysis. In Microsoft Excel, tables are one of the most powerful tools to organize, analyze, and manage data efficiently.If you work with lists, sales data, attendance sheets, or reports — converting your data range into a table can save you time and improve accuracy. When you convert data range into a table, Excel will: Add filter arrows automatically Apply color formatting (based on the style you choose) Allow quick sorting and filtering Support automatic formula extension  

Conditional Formatting is one of the most powerful features in Microsoft Excel — it helps you visually analyze your data by applying colors, bars, or icons automatically based on cell values. Conditional Formatting in Excel allows you to automatically apply formatting styles (like colors, font styles, or icons) to cells that meet certain conditions or criteria. Conditional Formatting Menu Options As shown in the image, the Conditional Formatting dropdown menu includes several tools: Highlight Cells Rules Top/Bottom Rules Data Bars Color Scales Icon Sets New Rule Clear Rules Manage Rules Let’s understand each one in detail   Highlight Cells Rules This is the most commonly used feature. It allows you to highlight specific cells based on conditions such as: Greater Than / Less Than / Between Equal To Text That Contains A Date Occurring Duplicate Values Example: To highlight all marks greater than 80:   Top/Bottom Rules This option highlights top or bottom performing values in your data. Options include: Top 10 Items Top 10% Bottom 10 Items Bottom 10% Above Average Below Average Example: Highlight the Top 10 Sales in a dataset:   Data Bars Data Bars visually represent the magnitude of a cell’s value through horizontal bars. The longer the bar, the higher the value. Example: In a sales column, applying Data Bars shows larger bars for higher sales and smaller bars for lower ones., Perfect for dashboards, financial reports, and trend analysis.   Color Scales Color Scales apply gradient colors based on cell values — from lowest to highest. Excel automatically assigns colors, for example: Green to Red Scale: Green for high values, red for low. Blue to White Scale: Blue for large numbers, white for small. Example: In a “Profit” column, applying a Green-Yellow-Red scale quickly shows which products are performing best., Great for profit/loss analysis, scores, and data comparison.   Icon Sets Icon Sets use symbols or icons (like arrows, flags, traffic lights) to represent data categories. Example: 🟢 Up arrow for increasing sales 🟡 Side arrow for stable sales 🔴 Down arrow for decreasing sales Ideal for KPI dashboards and trend tracking.   New Rule… This allows you to create custom conditional formatting rules. You can define your own conditions using formulas. Example: Highlight all even numbers:   Clear Rules Removes all or selected conditional formatting from your data. Clear Rules from Selected Cells Clear Rules from Entire Sheet Example: If your formatting looks cluttered, use this to reset and reapply fresh rules.   Manage Rules This option helps you view, edit, copy, or delete existing rules. You can also change the order of rules or apply them to other ranges.  

Conditional Formatting is a powerful Excel feature that automatically changes the appearance of a cell — such as its background color, text color, or font style — based on the value it contains. When working with large datasets in Excel, it can be difficult to quickly identify important values — like the highest sales, overdue payments, or low scores. It helps you visually analyze data by automatically applying colors, icons, or styles to cells based on specific conditions. One of the most useful features of conditional formatting is Highlight Cell Rules — which allows you to highlight cells that meet certain criteria, such as greater than, less than, equal to, between values, or containing specific text. What are Highlight Cell Rules? Highlight Cell Rules are the most commonly used option within Conditional Formatting. They allow you to apply formatting rules to cells that meet specific conditions or comparisons. You can highlight cells based on: Greater than or Less than a value Between two values Equal to a specific number or text Text that contains certain words Dates that fall within a range Duplicate or Unique values 1. Greater Than… Highlights all cells containing values greater than the specified number., If you want to highlight all sales above ₹50,000, All sales above ₹50,000 will be automatically colored (e.g., light green). 2. Less Than… Highlights all cells containing values less than a specified number., Highlight all students who scored below 40 marks. 3. Between… Highlights cells that contain values between two numbers., Highlight sales between ₹30,000 and ₹60,000. 4. Equal To… Highlights cells equal to a specific number, word, or text., Highlight all orders with status “Pending”. 5. Text That Contains… Highlights cells that contain certain text or keywords., Highlight all customers whose names contain “Enterprises”. 6. A Date Occurring… Highlights cells that contain dates falling within a specific time range such as yesterday, today, last week, next month, etc., Highlight all tasks due next week. 7. Duplicate Values… Highlights cells that contain duplicate or unique values., Highlight duplicate invoice numbers or repeated names.   How to Customize Formatting When you select a rule (like Greater Than), Excel gives you some pre-made styles such as: Light Red Fill with Dark Red Text Yellow Fill with Dark Yellow Text Green Fill with Dark Green Text You can also create a Custom Format for font, color, border, or background style.

The Number Group is located in the Home Tab on the Excel ribbon. It includes options that allow you to change how numbers are displayed — such as General, Number, Currency, Accounting, Percentage, Fraction, Date, Time, and more. When you enter data in Excel — like prices, percentages, or dates — they may not always look the way you want. Sometimes you want to show currency symbols, sometimes decimal places, and sometimes even percentages or fractions.   Number Format Dropdown This is the main tool in the Number Group. You’ll find a dropdown list where you can choose different number formats like: General Number Currency Accounting Short Date / Long Date Time Percentage Fraction Scientific Text More Number Formats Example: If you type 25000 and choose Currency, Excel automatically converts it to ₹25,000.00. Tip: To access all formats, click the small arrow at the bottom-right corner of the Number Group — it opens the Format Cells dialog box.   General Format This is the default format in Excel. Numbers appear as you type them — without any commas, symbols, or decimals. Example: If you enter 5000, it will show as 5000 (plain number). Use this when you don’t need any special formatting.   Number Format Displays numbers with decimal places and commas. You can control how many decimal places you want to show. Example: Typing 1234.567 and setting 2 decimal places → shows as 1,234.57 Shortcut: Use Increase Decimal or Decrease Decimal buttons in the Number Group to adjust decimal points.   Currency Format (₹) Adds a currency symbol (like ₹, $, €, etc.) before the number and keeps decimals aligned. Example: 2500 → ₹2,500.00 Use this format for financial data such as prices, income, or expenses. Tip: Currency format keeps the symbol close to the number (₹2,500), while Accounting format aligns symbols in one column.   Accounting Format Similar to Currency, but aligns currency symbols and decimal points neatly in a column. It’s perfect for financial statements. Example: Item Amount Rent ₹10,000.00 Salary ₹25,000.00 Total ₹35,000.00 Shortcut: Click the ₹ symbol in the Number Group to apply Accounting format quickly. Percentage Format Converts a decimal number into a percentage instantly. Example: 0.75 → 75% Shortcut: Select cells → click the % icon in the Number Group. Tip: You can increase or decrease decimal places to show percentages more precisely (e.g., 75.50%).   Comma Style Adds thousands separators (commas) to large numbers — making them easy to read. Example: 2500000 → 2,500,000 Shortcut: Select cells → click the Comma Style (,) icon in the Number Group.   Increase / Decrease Decimal These two small buttons let you adjust the number of digits after the decimal point. Example: 123.4567 Increase Decimal → 123.4567 Decrease Decimal → 123.46   Date and Time Formats You can format numbers to display dates and times automatically. Example: Typing 14-10-2025 → Excel displays 14-Oct-2025 Typing 12:30 → Excel shows 12:30 PM Tip: Use Short Date for compact format and Long Date for full form (e.g., “Tuesday, October 14, 2025”).   Fraction and Scientific Formats Fraction: Displays numbers as fractions (e.g., 0.5 → 1/2). Scientific: Shows numbers in exponential form (e.g., 123000 → 1.23E+05). These are used in scientific, mathematical, or engineering data.   Example: Financial Data Formatting Description Value (General) Value (Formatted) Sales 250000 ₹2,50,000.00 Tax Rate 0.18 18% Date 14-10-2025 14-Oct-2025 Net Profit 1234.567 ₹1,234.57 This example shows how the Number Group helps make your data more meaningful and visually appealing.  

क्या आप जानना चाहते हैं कि एक Accountant कंपनी में रोज़ाना कैसे काम करता है?इस ब्लॉग में हम आपको न केवल बताएंगे कि TallyPrime में GST सेटअप कैसे करें, बल्कि आप एक प्रोफेशनल इनवॉइस बनाना, Stock Create करना और GST Purchase Entry करना भी सीखेंगे।

Why salary structure matters A well-planned salary structure does three big things: Statutory compliance – meets laws on PF, ESI, income-tax, professional-tax, gratuity, bonus, etc. Tax optimisation – splits pay into taxable and tax-free/reimbursable heads so employees take home more without raising company cost. Cost transparency – breaks “Cost-to-Company (CTC)” into fixed, variable and long-term benefits so budgeting and reporting are straightforward.   Key salary terms (glossary) Term What it represents Quick formula / rule CTC (Cost-to-Company) All annual money the employer spends on one employee, including statutory employer contributions & benefits CTC = Gross Pay + Employer PF + Employer ESI + Gratuity + Insurance + Perks Gross Pay Total earnings before employee-side deductions Gross = Sum of all earning heads (Basic, HRA, etc.) Net Pay / Take-Home Money credited to bank Net = Gross Pay – Employee PF – Employee ESI – Professional Tax – TDS – Other deductions Basic Salary Core earning, basis for many allowances & statutory deductions Often 35-60 % of Gross DA (Dearness Allowance) Inflation offset, common in public sector % of Basic HRA (House Rent Allowance) Helps with housing cost; partly tax-free Usually 40 % of Basic (non-metro) or 50 % (metro) Special / Flexi allowance Balances the structure so Gross aligns to budget Residual amount after all fixed heads Reimbursement Paid on bills—e.g., fuel, phone—mostly tax-exempt up to limits Actual spend (capped) Perquisite Non-cash benefit with cash value—e.g., car, ESOP Taxed as perquisite value   Common salary pay-heads Earnings (add to Gross Pay) Basic Salary Dearness Allowance (DA) House Rent Allowance (HRA) Conveyance Allowance Leave Travel Allowance (LTA) Medical Allowance / Medical Reimbursement Special / Supplementary / Flexi Allowance Food Coupons / Meal Allowance Overtime Pay Performance Bonus / Incentive Sales Commission Shift Allowance / Night Allowance Project Allowance / Site Allowance Uniform / Washing Allowance Child Education Allowance Internet / Mobile Phone Reimbursement Car / Driver Allowance Statutory Bonus (under Payment of Bonus Act) Ex-gratia   Employer contributions (part of CTC but not Gross) Employer Provident Fund (EPF) – 12 % of Basic (or of ₹15 000 ceiling in India if opted) Employer Employee State Insurance (ESI) – 3.25 % of Gross (if gross ≤ ₹21 000) Gratuity Accrual – 4.81 % of Basic (i.e., 15/26 × Basic × 1 year) National Pension System (NPS) / Superannuation Group Health / Term Insurance premium Leave Encashment Provision Company-paid Professional Development fund   Employee-side deductions Employee Provident Fund – 12 % of Basic (or 10 % in some orgs) Employee ESI – 0.75 % of Gross (if applicable) Professional Tax – slab-based (state law) Income-tax (TDS) – as per slab minus rebates/reductions Loan/Advance recovery (salary loan, festival advance) Voluntary NPS Contribution Union / Association subscription LIC premium through payroll, etc.   Core formulas you’ll encounter Component Typical formula / rule of thumb Basic 40 – 50 % of Gross (private sector); some MNCs keep 30 – 35 % to improve take-home DA Public sector: 38 % of Basic (rate revised twice yearly); Pvt. sector often “0” HRA Metro city: 50 % × Basic; Non-metro: 40 % × Basic Conveyance Allow. Fixed (e.g., ₹1 600 / month) or clubbed into special allowance after FY 2019 in India PF (Employee & Employer) 12 % × Basic (mandatory) or 12 % × ₹15 000 cap if employer chooses “PF on ceiling” ESI Employee 0.75 % × Gross, Employer 3.25 % × Gross (only if Gross ≤ ₹21 000) Gratuity 4.81 % × Basic (provision) Statutory Bonus 8.33 % of Basic (min) if Basic+DA ≤ ₹21 000 Professional Tax (India) ₹200 / ₹208 / ₹250 per month based on state slabs Income-tax (New Regime 2025-26) Slab: 0 % ≤ ₹3 L, 5 % ₹3-6 L, 10 % ₹6-9 L, 15 % ₹9-12 L, 20 % ₹12-15 L, 30 % > ₹15 L (rebate ≤ ₹7 L) Note: Adjust formulas to your jurisdiction—e.g., 401(k) match in the US, CPP/EI in Canada, NI in the UK, CPF in Singapore.   Step-by-step worked example Scenario Employee A joins an Indian IT firm in Bengaluru. Offer: CTC = ₹6 00 000 per annum (₹50 000 per month). Company follows “PF on actual Basic,” Gratuity provision, no ESI (income > ₹21 000). Pay-head Rule Monthly (₹) Basic 45 % of Gross 19 500 HRA 40 % of Basic 7 800 Special Allowance Balancing figure 20 200 Conveyance Allowance Fixed 1 500 Medical Insurance (Company group cover) Employer cost (CTC, not Gross) 500 Employer PF 12 % × Basic 2 340 Gratuity 4.81 % × Basic 938 Employer Cost to Company Sum above 50 000 (matches offer) Employee deductions Deduction Formula Monthly (₹) Employee PF 12 % × Basic 2 340 Professional Tax Karnataka slab (> ₹15 000) 200 Income-tax (New Regime) Approx. see below 1 910 Total deductions 4 450 Income-tax approximation Annual taxable income = Gross Pay – Std. Deduction (₹50 000) – Employer portion of NPS (none) – HRA exemption (assume employee pays ₹10 000 rent, metro). HRA exempt = least of: Actual HRA = 7 800 × 12 = 93 600 50 % of Basic = 50 % × (19 500 × 12) = 1 17 000 Rent – 10 % of Basic = (10 000 × 12) – 10 % × (19 500 × 12) = 1 20 000 – 23 400 = 96 600 → Exempt = 93 600 (least) Taxable HRA = 93 600 – 93 600 = 0. Taxable income ≈ 6 00 000 – 50 000 = 5 50 000 (falls under ₹7 L rebate zone → tax after rebate = ₹0). Yet surcharge & cess make monthly TDS ~₹0. (We used rough 1 910/month earlier to demonstrate deductions; with proper proof, TDS drops to 0.)   Net take-home pay Gross Pay = Basic + HRA + Special + Conveyance = ₹49 000 Net Pay ≈ Gross – Deductions = 49 000 – 4 450 ≈ ₹44 550   Designing a salary structure: best-practice checklist Keep Basic realistic – Too low violates PF