Data Archives - Excel-Help

Mar32023 by excelhelp13No Comments

Create Dynamic Dropdown list with Indirect formula

A Dynamic dropdown list or dependent dropdown list is very useful while working in Excel. Which makes our work easier and time get saved. Here we have an example of how to create a dynamic dropdown list or dependent dropdown. Example- We have names of some states and their cities. While working in Excel, you should create a dropdown list so that you do not have to type the name of the state or it's city repeatedly. If we select the state name in dropdown the list of cities of that state will appear automatically. We have the following data. In which dropdown list is to be created in column F3 to G6. Steps- To create a dropdown list of state names Select F3 to F6 In the Data tab, Click on Data Validation. In the dialog box that opens, select “L...

Jun162022 by excelhelp13No Comments

Create drop down List in Excel.

Data, tips & Tricks

STEPS : Prepare the list for drop-down in Excel sheet.Select the cell or range where you want to create a drop-down.Go to View tab.Click on Data Validation.In the Data Validation dialogue box, . Choose “List” from Allow drop-down.Type or Select the range of source data in source box.Click OK Note- you can create a drop-down in same sheet or in other sheet and can pass the referecne according to the requirement. See the Result in Below Image. Remove/Delete Drop-Down in excel. Select Cell or Range, who has drop-down that you want to Delete.Go to view tab.Click on Data Validation.In the Data Validation dialogue box, Click on Clear All.Click OK. See the Result in 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...

Mar282021 by excelhelp13No Comments

SPLIT CONTENTS IN DIFFERENT CELL IN EXCEL

Data, Menu Bar

STEPS : Select the cell or column that contains the text you want to split.Click on Data TabClick Text to Columns.In the Convert Text to Columns Wizard, select Delimitedclick Next. Select the Delimiters for your data. For example Comma, Space etc. You can see a preview of your data in the Data preview window. here we choose spaceClick Next. Choose the Column data format or use what Excel choose for you. Here we choose General.Select the Destination, which is where you want the split data to appear on your worksheet.Click finish

Mar242021 by excelhelp13No Comments

Remove Duplicates in excel

Data

Steps: 1. Select data or Click any single cell inside the data set. 2. Click on the Data tab 3. Click Remove Duplicates in the Data Tools group. 4. Remove duplicates dialog box appears. 5. Click check boxes as per your requirement and click OK. (in this example, remove rows with the Name. So, Check Name and click OK.) 6. Result- Excel removes all rows with the same Name except for the first instances found.