top of page

Thanks for submitting!

Excel || 20 Lesson Of Excel Must-Know Functions to Maximize your Productivity 100x

Writer's picture: Nancy DahiyaNancy Dahiya

Unlock Excel's Full Potential with these 20 Must-Know Functions:


(1) XLOOKUP

(2) Pivot Tables

(3) Wildcards

(4) Sparklines

(5) Duplicate & Remove Duplicates

(6) Transpose

(7) Filter & Slice

(8) Conditional Formatting

(9) Auto-fill

(10) TRIM


🧵11-20 with step-by-step examples:


Top 20 Excel Tips & Functions You Can't Afford to Miss:


(11) SUMIF

(12) SUMIFS

(13) COUNTIF

(14) COUNTIFS

(15) Goal Seek

(16) Charts

(17) UPPER, LOWER, PROPER

(18) CONVERT

(19) Stock Market data

(20) Geography / Maps


🧵Pictures & examples of each function:



What is Xlookup in Excel used for?

(1) XLOOKUP


This data search function allows you to search & retrieve data from ranges/tables with ease.


Use it to perform VLOOKUP & HLOOKUP tasks and much more.


=XLOOKUP("apple",A1:A5,B1:B5)


What is a pivot table in Excel used for?

(2) Pivot Tables


Get insights from your data in seconds with Pivot Tables.


Analyze large data sets and group, summarize, and transform your data into meaningful insights.


To access this function, go to "Insert" in the Menu bar, and then select "Pivot Table"

What are the wild card characters in Excel?

(3) Wildcards


Use wildcards in Excel formulas for partial text matches.


Excel has three wildcards: "", "?", and "~".


Example: Use "" for multiple characters, "?" for a single character, and "~" as an escape character in formulas.




What is sparkline function in Excel?

(4) Sparklines


Visualize your data like a pro with Sparklines


Quickly create mini charts within cells to visualize trends in your data.


On the 'Insert tab', click 'Sparklines'


=SPARKLINE(A1:A10,{'charttype','column'})



(5) Duplicate & Remove Duplicates


Quickly identify and eliminate duplicate entries in your data.


Select data, go to 'Data' Tab > Remove Duplicates.



(6) Transpose


Quickly switch rows to columns or columns to rows for better data analysis.


Select data, copy, go to another cell, right-click > Transpose.



(7) Filter & Slice


Quickly sort and isolate specific data for efficient and effective analysis.


Select data, go to 'Data Tab' > Filter


The shortcut for this function is CTRL + SHFT + L



(8) Conditional Formatting


Conditional Formatting in Excel allows you to highlight important data based on rules you define, making your data visually appealing and easier to show patterns and trends in your data.


Go to: Home –> Conditional Formatting –> Highlighting Cell Rules




(9) Auto-fill


AutoFill duplicates values. In large data sets, instead of typing formulas multiple times, use auto-fill


There are 3 ways to do this:


1) Double-click on the lower right corner of the 1st cell

2) Highlight a Section and type Ctrl + D

3) Drag the cell down the rows



(10) TRIM


The TRIM function removes extra spaces from text.


Examples include removing leading and trailing spaces, cleaning up data imported from other sources.


=TRIM()



(11) SUMIF


The SUMIF function allows you to sum values in a range based on specified criteria.


Use it to total values in a range that meet a certain condition.


Examples include summing sales for a specific region or calculating the total expenses for a specific category.




(12) SUMIFS


The SUMIFS function sums values based on multiple criteria.


Use it to sum values in a range that meets multiple conditions.


Examples include summing sales for a specific region and time period or calculating the total expenses for multiple categories.




(13) COUNTIF


The COUNTIF function allows you to count the number of cells in a range that meet a specific criterion.


Examples include counting the number of sales for a specific region or finding the number of occurrences of a specific text value.



(14) COUNTIFS


This function counts cells based on multiple criteria


Use it to count the number of cells in a range that meet multiple conditions


Examples include counting sales for multiple regions and time periods or finding the number of occurrences of multiple text values.



(15) Goal Seek


Goal Seek solves an unknown input in a formula.


It finds the input value that produces a desired result.


Examples include finding the interest rate for a loan to reach a specific monthly payment, or determining the quantity needed to reach a target sales goal.



(16) Charts


Charts help visualize data. Use the "Insert Chart" option to select a chart type (bar, pie, line, etc.), and then select the data range.


Examples: Create a bar chart to compare sales data.


Select 'Insert Chart', 'Bar Chart', and highlight your sales data range.




(17) UPPER, LOWER, PROPER


These functions change the case of text in a cell.


UPPER converts text to all caps, LOWER to all lowercase, and PROPER to proper case (first letter of each word capitalized).


Use it by typing =UPPER(cell), =LOWER(cell), or =PROPER(cell).



(18) CONVERT


This function allows you to change a number from one unit of measurement to another.


Examples: "Convert inches to centimeters with the CONVERT function.


Type =CONVERT(number, "in", "cm") and replace 'number' with the desired value."



(19) Stock Market data


This allows you to retrieve real-time information about stocks, such as current price, high, low, etc.


Enter a list of stock ticker symbols. then select the cells and go to the Data tab, then click the Stocks button within the Data Types group.



(20) Geography / Maps


With the Geography data type, you can retrieve data like population, time zone, area leaders, gasoline prices, language, and more


Type the data you need, then go to Data Tab -> Geography



BONUS:

You can use Microsoft Excel to create art





  • how to remove duplicates in excel

  • how to merge cells in excel

  • how to freeze a row in excel

  • how to lock cells in excel

  • how to find duplicates in excel

  • how to create a drop down list in excel

  • how to add drop down list in excel

  • how to create drop down list in excel

  • how to use excel

  • how to use vlookup in excel

  • how to unhide columns in excel

  • how to delete duplicates in excel

  • how to subtract in excel

  • how to enable macros in excel

  • how to wrap text in excel

  • how to password protect an excel file

  • how to alphabetize in excel

  • how to multiply in excel

  • how to make a graph in excel

  • how to unhide all rows in excel

  • how to create a pivot table in excel

  • how to combine cells in excel

  • how to make a drop down list in excel

  • how to add a column in excel

  • how to move columns in excel

  • how to add a drop down list in excel

  • how to sort in excel

  • how to separate names in excel

  • how to freeze panes in excel

  • how to add columns in excel

  • how to highlight duplicates in excel

  • how to hide columns in excel

  • how to find standard deviation in excel

  • how to do vlookup in excel

  • how to split cells in excel

  • how to do a vlookup in excel

  • how to sum a column in excel

  • how to calculate standard deviation in excel

  • how to unhide rows in excel

  • how to divide in excel

  • how to strikethrough in excel

  • how to combine two columns in excel

  • how to insert multiple rows in excel

  • how to convert pdf to excel

  • how to make drop down list in excel

  • how to compare two columns in excel

  • how to lock a row in excel

  • how to filter in excel

  • how to move a column in excel

  • what does this mean in excel

  • excel

  • microsoft excel

  • excel online

  • vlookup excel

  • google excel

  • pdf to excel convertor

  • pivot tables in excel

  • excel spreadsheet

  • merge cells on excel

  • excel formulas

  • pdf to excel

  • convert pdf to excel

  • how to remove duplicates in excel

  • excel definition

  • excel urgent care

  • excel energy

  • excel drop down list

  • how to merge cells in excel

  • excel vlookup

  • how to freeze a row in excel

  • unhide a column in excel

  • countif function in excel

  • index match excel

  • how to lock cells in excel

  • pdf to excel converter

  • gantt chart excel

  • remove duplicates excel

  • template budget excel

  • if function excel

  • macro enable in excel

  • concatenate excel

  • sumif function in excel

  • excel sheet

  • excel countif

  • excel remove duplicates

  • drop down list in excel

  • excel physical therapy

  • how to find duplicates in excel

  • conditional formatting excel

  • how to create a drop down list in excel

  • excel download

  • excel pivot table

  • how to add drop down list in excel

  • how to create drop down list in excel

  • how to use excel

  • vlookup in excel

  • how to use vlookup in excel

  • excel truck group

  • if then statements in excel

  • sumif excel

  • xlookup excel

  • how to unhide columns in excel

  • find duplicates in excel

  • how to delete duplicates in excel

  • countif excel

  • create drop down list in excel

  • sum excel column

  • excel high school

  • how to subtract in excel

  • merge cells in excel

  • standard deviation excel

  • how to enable macros in excel

  • how to wrap text in excel

  • excel functions

  • how to password protect an excel file

  • excel sumif

  • how to alphabetize in excel

  • excel shortcuts

  • inserting a check box in excel

  • download excel

  • excel to pdf

  • how to multiply in excel

  • microsoft excel online

  • how to make a graph in excel

  • wrap text in excel

  • excel sports

  • excel synonym

  • goal seek excel

  • excel macros

  • excel vba

  • excel conditional formatting

  • how to unhide all rows in excel

  • excel electric

  • excel concatenate

  • excel google

  • excel if statement

  • excel center

  • sumifs function in excel

  • excel academy

  • absolute reference excel

  • how to create a pivot table in excel

  • countifs function in excel

  • password protect excel

  • excel budget template

  • pivot table excel

  • how to combine cells in excel

  • excel boats

  • how to make a drop down list in excel

  • microsoft excel download

  • how to add a column in excel


Commentaires

Noté 0 étoile sur 5.
Pas encore de note

Ajouter une note
  • Whatsapp
©2024-2025 By S a r k a r i R e s u l t . E d u c a t i o n 
Unique Visitors
bottom of page