The protect range function is a useful way to prevent other users of a workbook from altering data within a worksheet. By using this function, you can prevent the accidental or intentional modification or deletion of worksheets or cells within a worksheet. The protection function can further be enhanced by password protection. The use of a password allows you to strengthen the security of your workbook.
As the owner of the workbook, you can set the rules on what worksheets and cells are off limits for modification.
This can be a valuable function if you have created a workbook that…
If you’re doing tutorials, walkthroughs or need to paste a screenshot In the Excel file to show a reference to something or support for something, the same can be done by using screenshot function of Excel. This function is occasionally more helpful than using the snipping tool because that tool occasionally closes an open window.
In this post I will explain you how to use screenshot insertion function with an example.
Below mentioned are the steps to insert a screenshot an Excel file:
The precedents and dependents function is used to graphically display and trace the relationships between cells and formulas with tracer arrows as shown in this figure below:
This is helpful in a couple of ways:
The terms precedents cells and dependents cells can further be explained with the help…
When the volume of data is humongous, sorting of data becomes essential. This helps in presentation of information in user friendly manner. Sorting can be done in multiple ways, ascending, descending, alphabetical, color, font and in numerous other ways.
In this post I will explain how to use sorting function in an Excel spreadsheet with the help of following example:
Example 1 — Suppose you are a trader of manufactured cars and want to sort the sales data as given in the table below according to the alphabetic sequence of name of manufacturers, following steps would be required to be…
Data validation is the function in Excel that is used to control what data can be input in a particular cell or range of cells.
When inputting the data, data validation can display a message for the user regarding what type/kind of data can be input.
It also stops the user from inputting the data other than that allowed by the data validation function and can give an error message when invalid data is entered.
Scenario Manager function in excel can be used for making different scenario such as good, bad, moderate, based on certain range of output/input which impact the ultimate outcome.
Data>Forecast>What-If Analysis>Scenario Manager
Text to column function is used to split cell contents into multiple columns depending on the criteria set. Exported data from a system contains a mix of data attributes. You need the data to be split up so that you can only the relevant part of it.
In this post I will explain you how to use text to column function in an Excel spreadsheet with the help of following example:
The HR Analyst received data exported from the company’s HR database containing employee name and phone number in the same cell. …
Remove duplicates function is used to remove duplicate values from a series of data in a table. This is a commonly used function if you need to find only unique values of a characteristic of data within a table.
If a data table contains an abundance of data and you want to make a list of the unique items only then this formula is often handy.
In this post I will explain how to use remove duplicates function in an excel spreadsheet with the help of following example:
Suppose the Financial Analyst received data exported from the company’s ERP system…
Name Manager function of excel is used to add, edit, delete and find the name of a particular cell, cell range, formulas, tables etc. This function increases the understandability of the formulas by giving the cell reference a particular name.
Go to: Formulas>Defined Names>Name Manager
Let’s understand how to use Name Manager Function. Suppose we want to name cell range A1:A10 as “Google”. We need to fill Name and Refers to fields of Name Manager as follows:
Refers to: A1:A10
Click on Name Manager and following dialog box will open:
INDIRECT function of Excel is used to give reference to the cell, cell range, any named range, work sheets or workbook. In simple words INDIRECT function is used to refer the cells, cell range of different work sheets or workbook in any formula and converts the text string into a valid cell reference. In this way, cell reference in a formula can be changed easily without changing the formula itself.
This function is useful when data set is spread across multiple work sheets or workbooks and we want to use this data on another work sheet or workbook.