Microsoft Excel Full Cheat Sheet. Here You Found Everything Ready-made Template Download Now..!!
1. Countif Formula
COUNTIF is an Excel function to count cells in a range that meet a single condition. COUNTIF can be used to count cells that contain dates, numbers, and text. The criteria used in COUNTIF supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.
2. Finding Mean, Median And Mode
-Average Function in Excel (Mean)
One of the most used statistical functions in Excel is Average. Calculating the Average in Excel is much simpler than it was in the school. Simply use the Average function and select the range which needs to be averaged. In the example below we want to know the average of the marks obtained by the students so we use =AVERAGE(B2:B12).
-Median in Excel
Median is a function which is used to find the middle number in a given range of numbers. When you are finding median manually, you need to sort the data in an ascending order but in Excel, you can simply use the Median function and select the range and you will find your median. We take the same example as above to find the median of marks obtained by students. So we use =MEDIAN(B2:B12).
-Mode in Excel
Mode helps you to find out the value that occurs most number of times. When you are working on a large amount of data, this function can be a lot of help. To find the most occurring value in Excel, use the MODE function and select the range you want to find the mode of. In our example below, we use =MODE(B2:B12) and since 2 students have scored 55 we get the answer as 55.
3. Finding Principle In Excel Sheet
To calculate the principal portion of a loan payment in a given period, you can use the PPMT function. In the example shown, the formula in C10 is:
=PPMT(C6/12,1,C8,-C5)
4. Finding Yes or No or Pass Fail
To return "Pass" when any 4 subjects have a passing score, and "Fail" when not, you can use a formula based on the IF and COUNTIF functions. In the example shown, the formula in I5 is:
=IF(COUNTIF(C5:H5,">=70")>=4,"Pass","Fail")
where 70 represents the passing score for all subjects.
5. Goal Seek In Excel
Goal Seek is a built-in Excel tool that allows you to see how one data item in a formula impacts another. You might look at these as “cause and effect” scenarios. It’s useful to answer “what if” type questions because you can adjust one cell entry to see the result. The tool is often used in finance, sales, and forecasting scenarios, but there are other uses.
6. Groups And Subtotal In Excel
Worksheets with a lot of content can sometimes feel overwhelming and can even become difficult to read. Fortunately, Excel can organize data into groups, allowing you to easily show and hide different sections of your worksheet. You can also summarize different groups using the Subtotal command and create an outline for your worksheet.
7. Monthly Payroll In Excel
•While most of the organizations turn their head towards software like Tally or ADP for payroll or they give a contract to some outsourcing companies to provide them the payroll services without any fuss. Several reasons are there for this approach. However, the core out of those is, it is really very time consuming to do all the manual work for payroll and have it set. Apart from that, it consumes manpower engaged as well. With all the ease it makes for them to be considered, there are still some businesses who prefer to do payroll work on their own (in-house) and manage it accordingly.
•Having already said it is time, cost and manpower consuming task, it provides you total control over your employee’s payroll as well as of each penny you are spending on them at the same time. In order to do payroll manually, you need something, (definitely a tool) that is powerful, versatile and easy to handle at the same time. With all these requirements, you can easily guess the tool. Yes, you might have guessed it right! Microsoft Excel. With the range of simple formulae and it’s simple layout makes excel standout for those who are doing payroll manually for their employee. In this article, we are going to see how we can create a payroll manually from scratch.
8. Pivot Table And Chart In Excel
A pivot table is a table of statistics that summarizes the data of a more extensive table (such as from a database, spreadsheet, or business intelligence program). This summary might include sums, averages, or other statistics, which the pivot table groups together in a meaningful way.
Pivot tables are a technique in data processing. They arrange and rearrange (or "pivot") statistics in order to draw attention to useful information. This leads to finding figures and facts quickly making them integral to data analysis. This ultimately leads to helping businesses or individuals make educated decisions.
9. Results Sheet In Excel
Every organization nowadays, whether it is any multinational company, small proprietorship, school or college, etc., uses MS Excel to maintain their data and analyze the data for making decisions. In schools, there are more than 1000 students in various standards and divisions. It is difficult to maintain their data manually in registers. That is why the management of schools uses MS Excel to maintain data of students. In the excel mark sheet, we have to manipulate the marks of students in various ways to evaluate their performance and give the result.
10. Vlookup&Hlookup In Excel
HLOOKUP and VLOOKUP are functions in Microsoft Excel that allow you to use a section of your spreadsheet as a lookup table.
When the VLOOKUP function is called, Excel searches for a lookup value in the leftmost column of a section of your spreadsheet called the table array. The function returns another value in the same row, defined by the column index number.
HLOOKUP is similar to VLOOKUP, but searches a row instead of a column, and the result is offset by a row index number. The V in VLOOKUP stands for vertical search (in a single column), while the H in HLOOKUP stands for horizontal search (within a single row).
Top Ten Excel Cheatsheet by Digitalise Data.
Thank You...!!!
0 Comments