tips & Tricks Archives

Mar182023 by excelhelp13No Comments

FLOOR Function in Excel

The FLOOR Function Rounds a Number Down to its Nearest Multiple of Significance. FLOOR Function helps us round Down the numbers as per the requirement. Syntax: =FLOOR(Number,Significance) Arguments: Number: This is the value that you want to Round Down. Significance: This is the Multiple that you want to Round Down. See the Examples in blow Image.

Mar42023 by excelhelp13No Comments

Dynamic Borders in Excel.

Home Menu, tips & Tricks

Excel has an inbuilt Feature to add borders automatically on your worksheet while you are entering value/data in a worksheet. Steps - 1. Select range or entire columns or entire rows or entire sheet. 2. Go to Home tab. 3. Click on Conditional Formatting. 4. Choose New Rule. 5. In the New Formatting Dialogue box, Click on “use a formula to determine Which cells to format” 6. Enter formula in the box- =A1<> “” Note - A1 is a first cell of selection 7. Click on Format 8. Go to Border tab. 9. Click on “outline” 10. Click OK 11. In the New Formatting Dialogue box, Click OK 12. See the Result in below Image. Entering the data automatically adds a border to that cell.

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:

Mar42023 by excelhelp13No Comments

Translate Text into a different Language in excel

Menu Bar, Review, tips & Tricks

Excel Translate is a function that converts text in one language to another language. Mostly English language is used to present the data. However, you can change the default English language content to your own language. Steps- Select cell and Go to Review Tab Click on Translate 3. On the right-hand side, you will see the dialog box. 4. In the Search for, You will see the text of the selected cell. Or type the word you want to convert. 5. Select From - To language in the dialog box. 6. Click on Start Searching button. 7. It will translate the in Hindi as it is the selected translate language for this example..You can choose your language as per requirement. 8. If you click on Insert, it will insert the converted text to the acti...

Mar32023 by excelhelp13No Comments

Create Dynamic Dropdown list with Indirect formula

Data, tips & Tricks

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

Hide Several Numbers in a Cell.

tips & Tricks, View

EXAMPLE : Suppose you have a list of some mobile numbers in your worksheet. You don’t want that list to be easily accessible to anyone. So you can hide some digits of all those mobile numbers in excel. STEPS : Select Cell or Range.Right Click on it.Click on Format Cells… 4. Click on Number tab, 5. Click on Custom. 6. In the Type box, Type one of the following options as per your requirement. 7. Click OK 8. See the Result in below Image Unhide Several Numbers in a Cell. STEPS : Select Cell or Range.Right Click on it.Click on Format Cells…Click on Number tab.Click on Custom.Select General from the Drop-Down.Click OK 8. See the Result in below Image