Top 10 Functions to use in Excel

 

Here’s a list of the Top 10 Excel functions that are most widely used and extremely useful for data analysis, reporting, and daily business tasks:

1. VLOOKUP / XLOOKUP

• VLOOKUP helps to search for a value in the first column of a range and return a value from another column.

• XLOOKUP (newer & more powerful) can search in both directions and replace VLOOKUP & HLOOKUP.

👉 Example: =XLOOKUP(105, A2:A10, C2:C10)

2. IF

• Used to perform logical tests and return different results based on TRUE or FALSE conditions.

👉 Example: =IF(B2>=50,"Pass","Fail")

3. SUM & SUMIF / SUMIFS

• SUM adds numbers quickly.

• SUMIF / SUMIFS add numbers based on single or multiple conditions.

👉 Example: =SUMIF(A2:A10,"Apples",B2:B10)

4. INDEX & MATCH

• A powerful combo that replaces VLOOKUP, allowing more flexibility.

• INDEX returns a value from a table by row & column number.

• MATCH gives the position of a value in a range.

👉 Example: =INDEX(C2:C10, MATCH(105, A2:A10, 0))

5. TEXT Functions (LEFT, RIGHT, MID, LEN, TRIM, CONCAT / TEXTJOIN)

• Help in cleaning and formatting text.

• LEFT/RIGHT extract characters.

• CONCAT / TEXTJOIN join text easily.

👉 Example: =TEXTJOIN(" ",TRUE,A2:A5)

6. PROPER, UPPER, LOWER

• Useful for formatting names and text consistently.

👉 Example: =PROPER("john smith") → "John Smith"

7. ROUND, ROUNDUP, ROUNDDOWN

• Round numbers to required decimal places.

👉 Example: =ROUND(123.456,2) → 123.46

8. TODAY & NOW

• Insert the current date/time dynamically.

👉 Example: =TODAY() → 06-Sep-2025

9. COUNT, COUNTA, COUNTIF / COUNTIFS

• COUNT → counts numbers.

• COUNTA → counts non-empty cells.

• COUNTIF / COUNTIFS → counts based on conditions.

👉 Example: =COUNTIF(A2:A10,"Apples")

10. PivotTables (not a single function, but a feature)

• Summarizes large datasets with drag-and-drop ease.

• Allows grouping, filtering, and aggregating data.

✅ If you’re learning Excel for business or data analytics, start with these.

Various Applications of VLOOKUP in Excel

Microsoft Excel is one of the most powerful tools for data management, and VLOOKUP (Vertical Lookup) is among its most widely used functions. It helps users search for a value in the first column of a table and return related information from another column. Though simple in concept, VLOOKUP has a wide range of practical applications across industries.

1. Employee Data Management
In HR, VLOOKUP can quickly pull employee details such as department, designation, or contact number by entering the employee ID. This saves time compared to searching manually through large spreadsheets.

2. Sales and Inventory Tracking
Businesses often use VLOOKUP to find product prices, stock levels, or supplier information. For instance, typing in a product code can instantly display its price and availability, making it easier to manage sales and reorder decisions.

3. Financial Analysis
Finance professionals use VLOOKUP to match customer IDs with outstanding balances, retrieve tax rates from slabs, or calculate loan eligibility based on predefined criteria.

4. Education and Academics
Schools and colleges rely on VLOOKUP for grading systems. Entering a student’s roll number can instantly bring up marks, grades, and attendance records.

5. Error Handling and Reports
Combined with functions like IFERROR, VLOOKUP can create cleaner reports by avoiding error messages and ensuring smooth data retrieval even with missing values.

6. Everyday Use
Even outside professional fields, VLOOKUP helps individuals organize personal budgets, price comparisons, or simple databases like contact lists.

In short, VLOOKUP is more than just a formula—it’s a productivity booster. Whether you are managing employees, analyzing sales, or preparing student results, VLOOKUP streamlines the process, reduces manual work, and ensures accuracy. Mastering it is a must for anyone serious about Excel.

Here’s a visual example you can add to your blog — a simple employee database table where VLOOKUP can be used to find an employee’s details (e.g., type =VLOOKUP(103, A2:D6, 2, FALSE) to get “Charlie”).

Here’s the second visual showing how VLOOKUP is applied in Excel:

• Enter 103 in cell A8.

• Use formulas like =VLOOKUP(A8, A2:D6, 2, FALSE) to fetch the employee’s Name, Department, or Salary.

 

 

 

 

 

List of Shortcuts in Excel

Shortcuts are keyboard combinations that can do specific actions in Excel 

Here’s a comprehensive list of Excel shortcuts categorized for easy reference, along with examples to illustrate their use.

Basic Shortcuts

Shortcut	Action	Example
Ctrl + N	Create a new workbook	Opens a blank Excel file.
Ctrl + O	Open an existing workbook	Opens the file selection window.
Ctrl + S	Save the workbook	Saves your changes to the file.
Ctrl + P	Print the workbook	Opens the print menu.
Ctrl + W	Close the workbook	Closes the current workbook.
Ctrl + C	Copy	Copies selected text or cell content.
Ctrl + X	Cut	Cuts selected text or cell content.
Ctrl + V	Paste	Pastes copied/cut data.
Ctrl + Z	Undo	Reverses the last action.
Ctrl + Y	Redo	Restores an undone action.

Navigation Shortcuts

Shortcut	Action	Example
Arrow Keys	Move between cells	Moves selection in the arrow's direction.
Ctrl + Arrow Key	Jump to the last non-empty cell in the direction	If A1 is selected, pressing Ctrl + ↓ moves to the last filled cell in column A.
Ctrl + Home	Go to A1	Jumps to the first cell (A1).
Ctrl + End	Go to the last used cell	Moves to the last used row and column.
Page Up/Page Down	Scroll one screen up/down	Scrolls through the worksheet.
Alt + Page Up/Page Down	Move one screen left/right	Helps navigate large sheets.

Selection Shortcuts

Shortcut	Action	Example
Ctrl + A	Select the entire worksheet	Selects all the data in the sheet.
Shift + Arrow Key	Extend selection	Expands the selected range.
Ctrl + Shift + Arrow Key	Select a range to the last non-empty cell	Selects from the current cell to the last filled cell in a direction.
Shift + Space	Select entire row	Selects the entire row of the active cell.
Ctrl + Space	Select entire column	Selects the entire column of the active cell.

Editing Shortcuts

Shortcut	Action	Example
F2	Edit active cell	Places the cursor inside the cell for editing.
Alt + Enter	Insert a new line within a cell	Adds a line break inside a cell.
Ctrl + D	Fill down	Copies the content of the topmost cell down.
Ctrl + R	Fill right	Copies the leftmost cell’s content to the right.
Ctrl + "-"	Delete selected cells/rows/columns	Removes the selection and shifts adjacent data accordingly.
Ctrl + "+"	Insert new cells/rows/columns	Adds new space to the sheet.

Formula Shortcuts

Shortcut	Action	Example
= (Equal Sign)	Start a formula	=SUM(A1:A10) sums the values in A1 to A10.
Ctrl + `	Show formulas instead of values	Toggles between formula view and normal view.
F4	Toggle absolute/relative cell reference	Changes A1 → $A$1 → A$1 → $A1 when editing a formula.
Alt + =	AutoSum	Quickly sums up a range.
Ctrl + Shift + Enter	Enter an array formula (for older versions)	Used to enter legacy array formulas.

Formatting Shortcuts

Shortcut	Action	Example
Ctrl + B	Bold	Makes text bold.
Ctrl + I	Italic	Italicizes text.
Ctrl + U	Underline	Underlines text.
Ctrl + 1	Open Format Cells dialog	Allows formatting of numbers, alignment, etc.
Ctrl + Shift + $	Apply currency format	Converts numbers to currency format (e.g., $123.45).
Ctrl + Shift + %	Apply percentage format	Converts a number into a percentage (e.g., 0.5 → 50%).
Ctrl + Shift + #	Apply date format	Formats numbers as a date (e.g., 03/15/2025).

Worksheet Shortcuts

Shortcut	Action	Example
Shift + F11	Insert a new worksheet	Adds a new worksheet to the workbook.
Ctrl + Page Up	Move to the previous worksheet	Switches to the previous sheet.
Ctrl + Page Down	Move to the next worksheet	Switches to the next sheet.
Alt + H + O + R	Rename a worksheet	Opens the rename option for the current sheet.

Data Handling Shortcuts

Shortcut	Action	Example
Ctrl + T	Create a table	Converts a range into a table.
Ctrl + Shift + L	Apply/Remove filters	Adds or removes filters on column headers.
Alt + Down Arrow	Open drop-down list in filtered column	Displays available filter options.
Ctrl + Shift + U	Expand or collapse formula bar	Adjusts formula bar visibility.

Pivot Table Shortcuts

Shortcut	Action	Example
Alt + N + V	Insert a PivotTable	Opens the Create PivotTable dialog.
Alt + J + T + R	Refresh PivotTable	Updates PivotTable data.
Alt + J + T + I	Insert PivotChart	Adds a chart to PivotTable.

Miscellaneous Shortcuts

Shortcut	Action	Example
F7	Spell check	Runs spell check on selected text.
F12	Save As	Opens the Save As dialog.
Ctrl + Shift + " (Quotation)	Copy value from the cell above	Copies the above cell's value into the active cell.
Ctrl + Shift + :	Insert current time	Inserts the system's current time in the selected cell.
Ctrl + ;	Insert current date	Inserts today's date.

This list covers most Excel shortcuts that boost productivity.

Excel is used extensively for Data collection, analysis and presentation of information in a user understandable format which can be easily understood by the normal user. It is the most commonly used application as it is easily accessible and available for all users. 

Get the Palium Advantage

• Regular & Fast Track Classes: Choose between weekday or weekend sessions to suit your schedule.

• Expert Faculty: Learn from experienced industry professionals with real-world expertise.

• Hands-On Learning: Practical training with real-time case studies and projects.

• Flexible Learning Modes: Available in offline (classroom) and live online formats.

• Placement Assistance: Get career support and job placement guidance.

Why Learn Advanced Excel?

Advanced Excel is a crucial tool for data analysis, financial modeling, reporting, and business decision-making. Mastering Excel can open opportunities in roles such as data analyst, business analyst, financial analyst, and MIS reporting.

Course Coverage

The course covers the following topics comprehensively -

• Conditional Formatting
• Visualizations
• Pivot Tables
• Formulas for Date, Text, Financial Calculations etc.
  
• Advanced Functions like Vlookup, XLookup, Match, Sumif, Sumifs etc.
  

Enroll Today!

Join Advanced Excel training at Palium Skills and gain the expertise to excel in your professional journey with comprehensive, practical, and job-oriented learning.

Take the next step in your professional journey with Palium Skills career building courses. Experience high-quality language training that ensures fluency and career growth.

📍 Location: Kolkata, India
📞 Phone: +919903130500
📧 Email: info@paliumskills.com
🌐 Website: https://paliumskills.com

Start learning Excel today and unlock global opportunities!

 

 

 

Understanding Conditional Formatting in Excel

 

 

Using Conditional Formatting in Excel

Conditional Formatting in Excel allows you to format cells based on specific conditions, making data analysis easier by highlighting key information.

1. Applying Conditional Formatting

Steps to Apply:

1. Select the range of cells you want to format.
2. Go to the Home tab → Click Conditional Formatting in the ribbon.
3. Choose a formatting rule based on your needs.

2. Common Conditional Formatting Rules

A) Highlight Cells Based on Values

• Example: Highlight sales greater than $10,000.
  • Click Conditional Formatting → Highlight Cells Rules → Greater Than…
  • Enter 10000 and choose a formatting style (e.g., red fill).

B) Use Color Scales for Data Ranges

• Example: Apply a color gradient from red (low) to green (high).
  • Click Conditional Formatting → Color Scales → Choose a style.

C) Use Data Bars to Visualize Data

• Example: Show progress in a dataset.
  • Click Conditional Formatting → Data Bars → Choose a fill style.

D) Apply Icon Sets

• Example: Show green, yellow, and red arrows for high, medium, and low values.
  • Click Conditional Formatting → Icon Sets → Choose a style.

E) Highlight Duplicates

• Example: Find duplicate names in a list.
  • Click Conditional Formatting → Highlight Cells Rules → Duplicate Values.

3. Custom Conditional Formatting with Formulas

You can use formulas to apply formatting dynamically.

Example 1: Highlight Rows Where Sales Exceed $10,000

1. Select the dataset (e.g., A2:D100).
2. Click Conditional Formatting → New Rule.
3. Select Use a formula to determine which cells to format.
4. Enter the formula:

   =B2>10000
   

5. Click Format, choose a color, and apply.

Example 2: Highlight Even Rows for Better Readability

1. Select your data range.
2. Click Conditional Formatting → New Rule.
3. Enter the formula:

   =MOD(ROW(),2)=0
   

4. Choose a light fill color and apply.

4. Managing and Removing Conditional Formatting

• View Applied Rules:
  • Click Conditional Formatting → Manage Rules.
• Remove Conditional Formatting:
  • Click Clear Rules and choose from the worksheet or selected cells.

Conclusion

Conditional Formatting is a powerful Excel feature that helps visualize trends, highlight important data, and improve data readability.

Would you like a custom VBA script for advanced formatting, or help with a specific scenario? 🚀

Create a Slicer in a Table in Excel

 Create a Slicer in a normal Table in Excel is the smart way to create a filter! It can be used while using a Tab, Laptop or a Desktop!

 

 

In Excel, Slicers are an interactive way to filter data in PivotTables and Tables (Excel Tables). They provide buttons you can click to filter data, making filtering more intuitive than drop-down lists.

Steps to Create a Slicer in a Normal Table in Excel

1. Convert Data into an Excel Table

   • Select your data range.
   • Press Ctrl + T or go to Insert → Table.
   • Check the box "My table has headers" and click OK.

2. Insert a Slicer

   • Click anywhere inside the Table.
   • Go to Table Design (or Table Tools) → Insert Slicer.
   • Select the column(s) you want to filter with the Slicer and click OK.

3. Use the Slicer

   • Click the buttons in the Slicer to filter data instantly.
   • Hold Ctrl to select multiple items.
   • Use the Clear Filter button in the Slicer to reset the filter.

Why Use a Slicer?

• Provides a visual way to filter data.
• Works on Touchscreens, Laptops, and Desktops.
• Easier to use than drop-down filters.
• Allows multiple selections at once.

Now you can quickly filter and analyze your data using Slicers in Excel Tables! 

 

 

Master Data Analysis & Automation with Advanced Excel Training

Looking to enhance your Excel skills for better career prospects?  

Palium Skills offers comprehensive Advanced Excel training, conducted by highly experienced and certified faculty members. Whether you are a professional seeking to improve your data analysis skills or a student aiming to boost your resume, our course is designed to provide in-depth learning with a hands-on approach.

Excel is used extensively for Data collection, analysis and presentation of information in a user understandable format which can be easily understood by the normal user. It is the most commonly used application as it is easily accessible and available for all users. 

Get the Palium Advantage

• Regular & Fast Track Classes: Choose between weekday or weekend sessions to suit your schedule.

• Expert Faculty: Learn from experienced industry professionals with real-world expertise.

• Hands-On Learning: Practical training with real-time case studies and projects.

• Flexible Learning Modes: Available in offline (classroom) and live online formats.

• Placement Assistance: Get career support and job placement guidance.

Why Learn Advanced Excel?

Advanced Excel is a crucial tool for data analysis, financial modeling, reporting, and business decision-making. Mastering Excel can open opportunities in roles such as data analyst, business analyst, financial analyst, and MIS reporting.

Course Coverage

The course covers the following topics comprehensively -

• Conditional Formatting
• Visualizations
• Pivot Tables
• Formulas for Date, Text, Financial Calculations etc.
  
• Advanced Functions like Vlookup, XLookup, Match, Sumif, Sumifs etc.
  

Enroll Today!

Join Advanced Excel training at Palium Skills and gain the expertise to excel in your professional journey with comprehensive, practical, and job-oriented learning.

Take the next step in your professional journey with Palium Skills career building courses. Experience high-quality language training that ensures fluency and career growth.

📍 Location: Kolkata, India
📞 Phone: +919903130500
📧 Email: info@paliumskills.com
🌐 Website: https://paliumskills.com

Start learning Excel today and unlock global opportunities!

Mastering Excel from Novice to Pro!

Hey everyone, I'm here today to talk about something that could really boost your career potential – mastering Excel. 

Now, if you're like me, you know that Excel isn't just about numbers; it's a powerful tool that can drive business decisions and growth. But how do you go from being just okay to a real pro? Let me introduce you to Palium Skills. … 

At Palium Skills, they offer comprehensive Excel training that takes you from the basics right through to advanced levels. This isn’t just another online tutorial. We're talking about expert-led, hands-on training that dives deep into everything Excel has to offer. … 

Imagine understanding complex functions like IF, SUMIF, SUMIFS, and not just that, but also mastering VLOOKUP, HLOOKUP, and XLOOKUP. And yes, they'll even walk you through the essentials of conditional formatting, charts, graphs, and more. 

Whether it's logical operations, data handling, or manipulating text, Palium Skills covers it all. … Now, here’s the best part - each participant finishes the course with a certificate. This isn’t just a piece of paper; it's a testament to your upgraded skills, a beacon on your resume signaling to potential employers that you're a proficient Excel user. … 

Classes are flexible too. Whether weekends or weekdays work best for you, Palium Skills accommodates with both regular and fast-track modes. They keep their batches small, ensuring you get the attention and guidance you need. … 

So, how do you get started? Simple. Call Palium Skills today at 7044871915 or 8420594969. Don’t just take your career to the next level – Excel at it. Trust me, a little investment in your skills now could open up endless opportunities. Let's make those spreadsheets work for you! … 

Remember, in today's corporate world, being just good enough isn't good enough. Be exceptional. Master Excel. 

Call Palium Skills today at +91-7044871915 or WhatsApp us at +91-99031 30500. Let’s do this together. See you there!