FORMULAS Archives - Page 2 of 17

Mar42023 by excelhelp13No Comments

STDEV (Standard deviation) Function in excel

STDEV (Standard deviation) Function returns a number that tells you how far numbers are from their mean (average). Syntax=STDEV (number1, [number2], …)Argumentsnumber1 - First number or reference in the sample.number2 - [optional] Second number or reference. For example, suppose you have a group of 10 students, and you are recording their result in percentage. In this data, the average result is 65 %, and the standard deviation is 4 %. It means that most of the student's result is within 4 % of the average result (which would be 62-69 %).

Mar42023 by excelhelp13No Comments

How to Permanently delete hidden Rows and Columns in Excel.

More Functions, tips & Tricks

Sometimes when working on Excel data, users hide unwanted multiple rows and columns. Which they do not want to be visible. And then deleting those multiple hidden rows and columns inside the data after the work is done is a time consuming task. Excel has an inbuilt function to find and delete hidden rows and columns at once. Steps - 1.Go to "File" Tab 2. Click On "info" 3. Click on "check for issues" 4. Click on "inspect document" 5. In the Document Inspector dialogue box, Click on “Inspect” 6. It will show hidden rows and columns in the option of “Hidden rows and columns”. 7. Click on “Remove All” button in it. 8. Close the dialog box.

Mar42023 by excelhelp13No Comments

Delete hidden Worksheets Permanently in Excel.

More Functions, tips & Tricks

Sometimes when working on Excel workbook that contains many hidden worksheets which are not useful. Unhiding hidden sheets and deleting one by one is time consuming task. Excel has an inbuilt function to find and delete hidden sheets at once. Note- Deleted Sheets can't be restored. And Can't be undo. Steps- Go to "File" Tab. Click on "Info" Click on "Check for issues" Choose "Inspect document" 5. In the Document Inspector dialogue box, Click on “Inspect” 6. It will show number of Hidden sheets in the option of “Hidden Sheets”. 7. Click on “Remove All” button in it. 8. Close the dialog box.

Mar42023 by excelhelp13No Comments

Delete hidden Data Permanently in Excel.

More Functions, tips & Tricks

Sometimes when working on Excel Data there might be a situation when many cells contains hidden data, information, etc. which are not useful. Unhiding them and deleting one by one is time consuming task. Excel has an inbuilt function to find and delete hidden data at once. Note- Deleted data can't be restored. And Can't be undo. Steps - Go to File Tab. Click on "Info" Click on "Check for issues" Click on inspect document. 5. In the Document Inspector dialogue box, Click on “Inspect” 6. It will show Hidden data in the option of “document properties and personal information”. 7. Click on “Remove All” button in it. 8. Close the dialog box.

Mar42023 by excelhelp13No Comments

Combine data From Multiple cells into a single cell.

Text, tips & Tricks

You can combine two or more cells data in a single cell. To combine data From Multiple cells into a single cell, follow the steps below. Type = Select First cell you want to combine. Type & Type double inverted comma with space in between. " " Type & Select Second cell you want to combine. See the examples in the image below.

Mar42023 by excelhelp13No Comments

DATEDIF Function in Excel

Date And Time, FORMULAS, tips & Tricks

The DATEDIF function is useful for finding the difference between two date values in years, months or days. Syntax: =DATEDIF (start date,end date,unit) Arguments- Start date - The date from which you want to find the difference. End date - The date until which you want to find the difference. Unit - The time unit to use (years, months, or days). UnitResult"y"Difference in complete years"m"Difference in complete months"d"Difference in days"md"Difference in days, ignoring months and years"ym"Difference in months, ignoring years"yd"Difference in days, ignoring years Examples:

Jun122022 by excelhelp13No Comments

Get Month name from Date as Text in Excel.

Text, tips & Tricks

To get the Month name from Date as text, you can use text function with a custom number format within excel.. Syntax:- (1) =text(date,"mmm") or (2) =text(date,"mmmm") NOTE: "mmm" – to get the first 3 letters of the name of the month."mmmm" – to get the full name of the month An Examples are shown in the image below.

Jun22022 by excelhelp13No Comments

Subtotal All Function in Excel

Data, Menu Bar, Statistical

SUBTOTAL function is use to get a subtotal in a list or from database. SUBTOTAL function can either include or exclude values in manually hidden rows. By default, SUBTOTAL excludes values in rows hidden by a filter. The SUBTOTAL function automatically ignores other SUBTOTAL formulas that exist in references to prevent double-counting. Syntax:-=SUBTOTAL (function_num, ref1, [ref2], ...) Arguments function_num - A number that specifies which function to use in calculating subtotals within a list. See table below for full list.ref1 - A named range or reference to subtotal.ref2 - [optional] A named range or reference to subtotal. There are 11 functions available, each with two options, as seen in the table below. When function_num is between 1-11, SUBTOTAL includes cells that have...

Jun22022 by excelhelp13No Comments

VLOOKUP with SUBTOTAL

FORMULAS, Lookup & Reference

Please follow the step shown below with images. This can be used when you want to check or analyze the data with different criteria and luckily excel has two great function to make this possible VLOOKUP 2. SUBTOTAL. Let's begin. We have Stock details of LCD, Keyboard, Mouse, Printer etc. From which we need certain types of information separately. Which we will get using VLOOKUP with SUBTOTAL. Follow the steps below. Create a subtotal Functions table in your worksheet. To create a list of subtotal functions, select cell C20 or according to your choice and adjustment you can change cell until it reference correctly. Then Go to DATA tab >> Data Validation. Select list in the dialog box that opens. Select H5:H11 in the source. Click ok. 4. The list of...

Feb12021 by excelhelp13No Comments

DSUM Function To Sum For Specific Criteria

FORMULAS, Maths

SYNTAX : =DSUM ( Database , Field , Criteria) DATABASE: The Whole Table Or A range of Cells With Header.FIELD: The Label Or You Can Say Header Of The Table / RangeCRITERIA: Single Or Multiple Range With Header. NOTE : You Can Have The Field In Separate Column As This Example Have Or You Can Directly Pick Any Of Those Directly From The Table Header By Giving Reference. EXAMPLE 1 : EXAMPLE 2 : EXAMPLE 3 :