এক্সেল

MS Excel 2016
ExcelTable-of-ContentsExcel ExamplesExcel Tips

200 Examples

01. Find Duplicates: This example teaches you how to find and highlight duplicates (or triplicates) in Excel.

02. Drop-down List: Drop-down lists in Excel are helpful if you want to be sure that users select an item from a list, instead of typing their own values.

03. Vlookup: The VLOOKUP function is one of the most popular functions in Excel. This page contains many easy to follow VLOOKUP examples.

04. Histogram: This example teaches you how to create a histogram in Excel.

05. Regression: This example teaches you how to run a linear regression analysis in Excel and how to interpret the Summary Output.

06. Percent Change: The percent change formula is used very often in Excel. For example, to calculate the Monthly Change and Total Change.

07. Pareto Chart: A Pareto chart combines a column chart and a line graph. The Pareto principle states that, for many events, roughly 80% of the effects come from 20% of the causes.

08. Loan Amortization Schedule: This example teaches you how to create a loan amortization schedule in Excel.

09. Random Numbers: Excel has two useful functions when it comes to generating random numbers. The RAND and RANDBETWEEN function.

10. Remove Duplicates: This example teaches you how to remove duplicates in Excel.

11. Countif: The powerful COUNTIF function in Excel counts cells based on one criteria. This page contains many easy to follow COUNTIF examples.

12. Lock Cells: You can lock cells in Excel if you want to protect cells from being edited.

13. Standard Deviation: This page explains how to calculate the standard deviation based on the entire population using the STDEV.P function in Excel and how to estimate the standard deviation based on a sample using the STDEV.S function in Excel.

14. Count Unique Values: This example shows you how to create an array formula that counts unique values.

15. Gantt Chart: Excel does not offer Gantt as chart type, but it’s easy to create a Gantt chart by customizing the stacked bar chart type.

16. Budget: This example shows you how to create a budget in Excel.

17. Line Chart: Line charts are used to display trends over time. Use a line chart if you have text labels, dates or a few numeric labels on the horizontal axis.

18. Transpose: Use the ‘Paste Special Transpose’ option to switch rows to columns or columns to rows in Excel. You can also use the TRANSPOSE function.

19. Correlation: We can use the CORREL function or the Analysis Toolpak add-in in Excel to find the correlation coefficient between two variables.

20. Time Sheet: This example teaches you how to create a simple timesheet calculator in Excel.

21. Offset: The OFFSET function in Excel returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells.

22. Pie Chart: Pie charts are used to display the contribution of each value (slice) to a total (pie). Pie charts always use one data series.

23. If: The IF function is one of the most used functions in Excel. This page contains many easy to follow IF examples.

24. Data Tables: Instead of creating different scenarios, you can create a data table to quickly try out different values for formulas. You can create a one variable data table or a two variable data table.

25. t-Test: This example teaches you how to perform a t-Test in Excel. The t-Test is used to test the null hypothesis that the means of two populations are equal.

26. Advanced Filter: This example teaches you how to apply an advanced filter in Excel to only display records that meet complex criteria.

27. Frequency Distribution: Did you know that you can use pivot tables to easily create a frequency distribution in Excel? You can also use the Analysis Toolpak to create a histogram.

28. Scatter Plot: Use a scatter plot (XY chart) to show scientific XY data. Scatter plots are often used to find out if there’s a relationship between variable X and Y.

29. Anova: This example teaches you how to perform a single factor ANOVA (analysis of variance) in Excel. A single factor or one-way ANOVA is used to test the null hypothesis that the means of several populations are all equal.

30. Compare Two Lists: This example describes how to compare two lists using conditional formatting.

31. Compound Interest: What’s compound interest and what’s the formula for compound interest in Excel? This example gives you the answers to these questions.

32. Sumif: The powerful SUMIF function in Excel sums cells based on one criteria. This page contains many easy to follow SUMIF examples.

33. Bar Chart: A bar chart is the horizontal version of a column chart. Use a bar chart if you have large text labels.

34. Concatenate: Use the CONCATENATE function in Excel to concatenate (join) strings. Instead of using CONCATENATE, simply use the & operator.

35. Freeze Panes: If you have a large table of data in Excel, it can be useful to freeze rows or columns. This way you can keep rows or columns visible while scrolling through the rest of the worksheet.

36. Weighted Average: To calculate a weighted average in Excel, simply use the SUMPRODUCT and the SUM function.

37. Index and Match: Use INDEX and MATCH in Excel and impress your boss. Instead of using VLOOKUP, use INDEX and MATCH. To perform advanced lookups, you’ll need INDEX and MATCH.

38. Delete Blank Rows: This example teaches you how to delete blank rows or rows that contain blank cells.

39. Sumproduct: To calculate the sum of the products of corresponding numbers in one or more ranges, use Excel’s powerful SUMPRODUCT function.

40. Merge Cells: This page illustrates how to merge selected cells into one large cell. This can be useful if you want to make clear that a label in Excel applies to multiple columns.

41. Subtract: There’s no SUBTRACT function in Excel. However, there are several ways to subtract numbers in Excel. Are you ready to improve your Excel skills?

42. Percentage: Calculating percentages in Excel is easy. Percentage simply means ‘out of 100′, so 72% is ’72 out of 100’ and 4% is ‘4 out of 100’, etc.

43. Contains Specific Text: To check if a cell contains specific text, you can use the ISNUMBER and the SEARCH function in Excel. There’s no CONTAINS function in Excel.

44. Pmt: The PMT function in Excel calculates the payment for a loan based on constant payments and a constant interest rate. This page contains many easy to follow PMT examples.

45. Calculate Age: To calculate the age of a person in Excel, use the DATEDIF function and the TODAY function.

46. Wrap Text: Wrap text in Excel if you want to display long text on multiple lines in a single cell.

47. Indirect: Use the INDIRECT function in Excel to convert a text string into a valid reference. You can use the & operator to create text strings.

48. Multiply: To multiply numbers in Excel, use the asterisk symbol (*) or the PRODUCT function. Learn how to multiply columns and how to multiply a column by a constant.

49. Subtotal: Use the SUBTOTAL function in Excel instead of SUM, COUNT, MAX, etc. to ignore rows hidden by a filter or to ignore manually hidden rows.

50. Goal Seek: If you know the result you want from a formula, use Goal Seek in Excel to find the input value that produces this formula result.

51. CAGR: There’s no CAGR function in Excel. However, simply use the RRI function in Excel to calculate the compound annual growth rate (CAGR) of an investment over a period of years.

52. If Cell is Blank: Use the IF function and an empty string in Excel to check if a cell is blank. You can also use the IF function and the ISBLANK function.

53. Average: The AVERAGE function in Excel calculates the average (arithmetic mean) of a group of numbers.

54. Substring: There’s no SUBSTRING function in Excel. Use the MID, LEFT, RIGHT, FIND, LEN, SUBSTITUTE, REPT, TRIM and the MAX function in Excel to extract substrings.

55. Count Characters: Learn how to count the number of characters in a cell or range of cells and how to count how many times a specific character occurs in a cell or range of cells.

56 Sum: Use the SUM function in Excel to sum a range of cells, an entire column or non-contiguous cells.

57. Compare Two Columns: To compare two columns, use the IF, ISERROR and the MATCH function in Excel. You can display the duplicates or the unique values.

58. Divide: There’s no DIVIDE function in Excel. Simply use the forward slash (/) to divide numbers in Excel.

59. Remove Spaces: The TRIM function in Excel removes leading spaces, extra spaces and trailing spaces. Use the SUBSTITUTE function to remove all spaces or non-breaking spaces.

60. Calendar: This example describes how to create a calendar in Excel (2020 calendar, 2021 calendar, etc). If you are in a hurry, simply download the Excel file.