Author

nidhi

Browsing

Understanding Absolute, Relative, and Mixed Cell References in MS Excel would help you work more efficiently and save a lot of time! Let us directly dive into understanding what are cell references and the types of cell references in MS Excel present to ease our work.

Introduction to Cell Referencing:

In Excel, a cell reference (example A1) is a combination of column name (i..e., A) followed by a row number (i.e., 1). By default settings, each cell in Excel is denoted with a column letter and a row number. While writing formulas, the user can refer to cells/ range of cells which is the ultimate power of cell references in MS Excel. For example, suppose we have the following data in cell A1 = 10 and cell A2 = 5. Let’s say you type formula as =A1+A2. Then Excel shall calculate the sum of values present in those cells which are 10+5 = 15.

Types of Cell References in MS Excel:

There are three types of cell references in MS Excel:

1.Relative Reference

2.Absolute Reference

3.Mixed Reference

1. Relative Cell Referencing in Excel:

A Relative cell reference changes when copying or moving the formula or cell value to a different location in the worksheet. Relative references are very useful when you have a range of cells and you want the references to relatively change as and when you copy the formula down to other cells.

Let us understand this with a small example.

Below is an image that displays the three different tables. The first contains the original Data, the second contains a Result that appears using the formula (i.e. cell referencing), and the third table portrays the formula that we used (i.e. the cell addresses).

1. In the cell G5 below we try to link it with C5

2. It gives the same value that is present in cell C5 i.e. the number 5

3. When you copy the link from G5 TO H6, the reference shall relatively change from C5 TO D6

Cell References in MS Excel

2. Absolute Cell Referencing in Excel:

An absolute cell reference does not change while copying or moving the formula to a different location in the worksheet. The cell references are fixed. Now you would wonder how can you fix cell reference?

To fix the cell references, we need to add a Dollar sign ($) before the column name and the row number by pressing the key F4. The F4 Key will allow you to add a dollar sign automatically before the cell references.

When a dollar sign is added before the column name and row number it fixes the references, as in stops the references from changing when copying the formula to the other cells.

Let us understand this with an example.

  1. In the below image, in cell G5, we link cell C5. But we want to fix the cell reference so that it does not change when copying the formula to other cells. To fix the cells press the key F4, which will insert a dollar symbol before the column name and row number

2. It returns the same value as in cell C5. Now when you copy the formula from G5 to H5, it still returns the same value as in C5, since the reference is now fixed and shall not change when copying the formula

Cell References in MS Excel

3. Mixed Cell Reference in Excel

Mixed cell reference allows fixing either the column name or the row number in a cell reference. That is one shall be relative and one shall be fixed.

There are two types of mixed cell references:

a. Fixed Row and Relative Column (i.e. the row reference is fixed but the column reference changes when the formula is copied to another location)

b. Relative Row and Fixed Column (i.e. the row reference changes but the column reference is fixed when a formula is copied to another location). When you use mixed references, the dollar sign shall be fixed only either before the column name or row number.

Let us understand this with an example:

a. Example of Mixed Referencing- Fixed Row and Relative Column

1.In the cell G5, we try to link the cell C5, but we do want row 5 to be fixed and not change when we copy the formula to other location, hence prefix a dollar sign before the row number 5

2.Now when copied the formula to cell H6, you would notice that the column reference has changed to “D”, but the row reference remains the same as “5”

Cell References in MS Excel

b. Example of Mixed Referencing- Relative Row and Fixed Column

1.In the cell G5, we try to link the cell C5, but we do want the column C to be fixed and not change when we copy the formula to other location, hence prefix a dollar sign before the Column name “C”

2.Now when copied the formula to cell H6, you would notice that the column reference is fixed to “C”, but the row reference has changed from “5” to “6”

Cell References in MS Excel 1

Follow our Youtube channel for more updates.

Visit our website for more updates on MS Excel

We have learned that Pivot Tables automatically update when a field is added to the Rows, Columns, Filter, or Values area of Pivot. This isn’t feasible if you have larger datasets, since it may take a longer time to complete every update. This is where Excel Pivot Table has provided us with an amazing option of Defer Layout Updates. In simple words, you can add or move all the fields to the Pivot Table Layout i.e. complete the entire layout changes, and then you can re-calculate the pivot table.

So let us now learn how to control when our Pivot Tables need to be updated.

Defer Layout Update Option in Pivot Table

To activate the options click on the checkbox that says “Defer Layout Update” below the Pivot Table fields. This will now allow you to add, remove or modify your pivot Tables field without actually updating it.

Defer Layout Update in Pivot Table 2

How does it work?

Now that we have activated the option, let us understand how does this works. Below is a Pivot Table which contains Customer Names in rows and their respective Sales Amount, Region-wise in Columns.

Defer Layout Update in Pivot Table 3

Now if you wish to modify your pivot table by adding or moving more than one field, you can check the Defer Layout Update box. By doing this the Pivot Table won’t be auto-calculated. In the below example you can see we have added a new field as “Order Date” in the Rows, but the pivot has not auto-updated.

Defer Layout Update in Pivot Table 4

Once you are done with setting up the fields you can click on the “Update” button which is towards the right of the Defer Layout Update box, which will now update or recalculate the Pivot Table. You can remove the checkmark from the Defer Layout Tab as well.

You can now add, remove or modify fields as many times as you want and when you are done setting up the fields, you can finally click on the Update button. This is extremely helpful when you have larger data-sets wherein you require to change fields often.

Excel LET function helps in assigning names to the variables that are repeatedly used in a formula.

Purpose

Assign a name to the calculation results in a formula. Useful for storing intermediate calculation and values by defining a name inside the formula. This makes formula easy to read and understand.

Return value

Returns the normal formula result

Syntax

=LET (name1, name_value1, calculation_or_name2,[name_value2…)

Arguments

ArgumentsDescriptionRemarks
name1First name to assign to a value or cell referenceMandatory
name_value1The value which is assigned a name1Mandatory
calculationA calculation that uses names within LET FunctionMandatory

Usage notes:

  • LET function can assign names to variables but these names can be used only within the scope of the function.
  • If you have to use the same expression or calculation multiple times in a formula, the LET Function assigns a name and allows you to call it by that name and use it multiple times in the formula. This makes it easier to read the formula and make it run faster.
  • LET uses about 126 name and name-value pairs
  • You cannot assign the same name twice in the function for different variables.
  • LET Function is not case-sensitive.

Availability

Microsoft 365

Example

Let’s say you have to use the tax rate “10%” multiple time in a formula, so instead of repeatedly typing it, you can assign a name to it. So we assume we assign the name “TaxRate” to it. So the formula works this way:

Formula used:  =LET(Tax,$C$5,D8*(1+Tax))

Tax: Name assigned to the value
$C$5: Name value i.e. 10%
D8*(1+Tax): Calculation. D8 is the revenue to be multiplied by the Tax Rate.

So here we aim at getting the Revenue amount including the tax rate, which returns the answer as shown in the below image:

Excel LET Function 5

Want to do some formatting, deleting, or editing options to your data that contains constants? Don’t know how to do these formatting? Then this article will help you to do these options using Go To Constants available in Go To Special Function. Let’s discuss it now.

Go To Constants

The simplest way to do some formatting to your data that contains constants is by using the “Go To Special” function. The “Go To Special” function allows to select cells based on criteria such as cell with constants, formulas, blank one.

1. Select any cell within the table and press Ctrl + A.

go to constants

Pro Tip. Select the cell within the table because if you select Ctrl + A outside the table, it will select the entire worksheet.

2. To choose the constants, go to Home > Find & Select > Go To Special. It will open a Go To Special dialog box.

go to constants

Pro Tip. You can also press F5 or Ctrl + G to open the Go To dialog box. Click on the special option to open the Go To Special dialog box.

3. In the Go To Special dialog box, select Constants and click OK.

As we want only the cells with constants that are text, select text and deselect all the other options.

go to constants

4. Once you click OK, the cells with constants that are text will be selected.

go to constants

Now, To select the cells with constants that are numbers, follow the same procedure but select numbers and deselect all other options and you can probably get this output.

Go To Constants using Go To Special 6

Note. The “Go To Constants” function does not highlight the cells with formulas.

Formatting and deleting:

Now, if you want to know the dates of the sales made during the particular month, you can click on the conditional formatting in the ribbon bar and click on the options available as per your requirements. The steps are:

1. Select any cell within the table and press Ctrl + A.

go to constants

Pro Tip. Select the cell within the table because if you select Ctrl + A outside the table, it will select the entire worksheet.

2. To do conditional formatting, go to Home > Conditional Formatting > Highlight Cells Rules > A date occurring. It will open a date occurring dialog box.

go to constants

3. In a date occurring dialog box, Choose the option for data that you want to highlight according to your preference and click OK.

Here I have selected “This Month” and “Light Red Fill with Dark Red Text” in the dialog box to know the dates of sales made during the current(August) month.

go to constants

4. Once you click OK, the changes will be made in the cells that contains date.

The below image will help you to comprehend better.

go to constants

Now, to only know the details of the product that have been sold, follow the procedure as below in the same worksheet.

1. Follow the same steps to open the “Go To Special” dialog box.

2. Once you have opened the dialog box, Choose Constants and select the Numbers option.

go to constants

3. Once you click OK, only the numbers columns will be highlighted.

go to constants

4. Now, Click delete on your keyboard and check your worksheet.

You can see my worksheet having no numbers and only the details of the products left.

go to constants

What happens when you select everything under “Go To Constants”?

Now, let’s see what the kind of output we get on selecting all the options under constants in the Go To Special dialog box.

1.Follow the same steps to open the “Go To Special” dialog box.

2. Select all the options and click OK.

go to constants

3. Once you click OK, everything will be displayed.

go to constants

Pro Tip. The technique of identifying some special cells have some applications. Errors and labels can be used to find the errors made in the cells containing formulas in your worksheet.

Hope you enjoyed reading Go To Constants. To know more about the shortcuts regarding Go to Special, Visit Here.

To know more about Excel go through our Formulas.

Do follow our YouTube channel for regular updates.

Looking for a piece of specific information but unable to search? Then let me introduce you to the Search Function in Excel. The Search function will enable to get to your information instantly.

Let’s begin our hunting journey with excel functions.

A quick view on the SEARCH Function in Excel

SEARCH function in Excel is a built-in function that returns the location (position) of a substring in a string. So, in simple words “SEARCH” function helps you to find the position of the letter that you are searching within a word.

SYNTAX:

=SEARCH ( find_text, within_text, [ start_num] )

How to use SEARCH function in Excel

1. SEARCH FUNCTION without specifying the position

To perform the SEARCH function in Excel, follow the steps given below.

Step 1: You need to enter the texts.

Step 2: Now, enter the syntax to perform the search function.

Using Search Function in Excel

Step 3: Press Enter Key.

How to use SEARCH Function in Excel 7

Here, the SEARCH function has returned the value “3” because I have searched for “c” in the cell “B6”. This means “c” is in the 3rd position within the searched string.

2. SEARCH function with specifying the position

In this next example, I have mentioned the position of the character from where the search has to begin. I have specified the position as 3 from where my search has to begin for the letter “n” within the cell “B6”.

Example 1

SYNTAX:

= SEARCH (“n”, B6, 3)

How to use SEARCH Function in Excel 8

Note:

It’s not a big deal even when you don’t specify the position because it will return you the same search result and that doesn’t makes a great difference.

Example 2

If you search for letter “l” from the 15th position, it will show you an error because there is no 15th position in this word.

SYNTAX:

= SEARCH(“I”,B6, 15)

How to use SEARCH Function in Excel 9

Here, I have searched for the substring “I” within the string “excel in excel” and I have commanded excel to perform the search result from 9th position. Since excel couldn’t find “I” when search is performed from 9th position, it has returned me an value error.

Example 3 (zero & negative numbers)

When you specify zero or negative numbers for the position from where the search has to be performed, then SEARCH function returns you an error.

SYNATX:

= SEARCH (“e”, B6, -5)

How to use SEARCH Function in Excel 10

3. SEARCH function with and without specifying position (repetition of words)

Example 1 (without specifying position)

SYNTAX: = SEARCH (“e”, B6)

How to use SEARCH Function in Excel 11

The syntax clearly depicts you that I have searched for the substring “e” within the string “excel in excel”.  SEARCH function has returned me the value “1” denoting the position of “e’ found in the first position. If you don’t mention the position, SEARCH function begins searching from the first and returns you the position of the searched letter as soon as it encounters it.

Example 2 (with specifying position)

SYNTAX: = SEARCH(“e”, B6, 7)

How to use SEARCH Function in Excel 12

Here, I have searched for the same letter “e” as depicted in the previous example. But, the only difference here is that I have specified the position from where the search has to be done. Since, I have specified the search to begin from 7th position, it ignores the “e” in first and fourth position and returns the value “10”.

4. A clear depiction of the differences between “FIND” and “SEARCH” function

The following two examples clearly depicts the difference between “FIND” and “SEARCH” function in Excel.

Example 1

SYNTAX:

=SEARCH (“e”, B6) for result 1.

= FIND (“e”, B7) for result 4.

How to use SEARCH Function in Excel 13

Here, you can clearly see that the SEARCH function has returned “1” for my search “e” and “FIND” function returned “4” for the same search “e”.

How to use SEARCH Function in Excel 14

This is because the “FIND” function is case-sensitive and ignores the “E” in the first position and takes into account the next “e”. Since the SEARCH function is case-insensitive, it has returned the position of “E” which is in the first position itself.

Example 2

Unlike FIND, SEARCH function in Excel also permits the usage of wildcard characters in the find_text argument.

SYNTAX:

= SEARCH (“are***excel”, B6)

How to use SEARCH Function in Excel 15

Here, I have searched for the word “are” and the SEARCH function has returned me the position of the beginning letter of the word “are” that is 4 (position of “a”).

 Exceptional cases

SEARCH function in Excel returns you #VALUE! if the substring that needs to be searched is not found within the body of the string.

SYNTAX:

=SEARCH (“a”, B5)

How to use SEARCH Function in Excel 16

Here, I have searched for “a” in the cell B6. Since, the word “excel in excel” doesn’t contain the letter “a” that is why SEARCH function has returned #VALUE! Error.

Key differences between “FIND” and “SEARCH” function

SEARCH function differs from FIND function in two ways

  1. SEARCH function in Excel is case-insensitive.
  2. It allows using wildcard characters.

For more information visit our YouTube channel.

Are your exported data dates are not in regional dates? Are you finding it difficult to replace this feature? Did you know the data type of your date can be changed? Then this article will help you to convert text to date format using Text to Columns option. Let’s get started.

Set Regional Dates with Text to Columns – An Introduction

Generally, when you download data from online like any financial data, the dates wouldn’t be of the same format. Though they view like dates, the data would actually be a text. To find whether the data is in text or date format, you can make use of a filter drop-down menu or number format drop-down menu.

Example: Here I have downloaded a financial file and all the dates are not in the region and I have to change it to regional dates. Now we will find whether the data is in text or date format using a filter drop-down menu. The steps for using the filter drop-down menu are as follows:

1. Select any cell within the table and go to Home > Sort & Filter > Filter.

regional dates

Pro Tip. You can also make use of the shortcut key to apply the filter. Select any cell and press Ctrl + Shift + L to apply the filter.

2. Once you click Filter, the filter will be applied to the headers in the form of drop-down menu.

regional dates

3. You can click on the drop-down arrow to see the filtering options for the headers.

Once I click the Date (Header) Filter, You can see the Date column is not grouped by month and year but all the dates are formatted in texts.

regional dates

The next option to find whether the data is in text or date format is by using a number format drop-down menu. The steps for using the number format drop-down menu are as follows:

1. Select any cell in the date column within the table and go to Home > number format drop-down menu.

regional dates

2. Once you click on the number format drop-down arrow, it will show you the list of options to format your cells.

Here, you can see that the dates and the numbers are the same in the formatting options. This is because Excel is not able to identify the data as a date.

regional dates

I will just show you an image of the formatting option which considers the cell as a date. The below image will help you to comprehend better.

regional dates

Convert Text into Data Format using Text to Columns Option:

The options like Find and Replace doesn’t really work in formatting dates because of the difference in regions. Alternatively, the Text to Columns feature works no matter which part of the world you are. The steps to convert text into date format are as follows:

1. Select the texts that you want convert into dates.

regional dates

Pro Tip. To do it quickly, Select the data first cell and press the shortcut key Ctrl + Shift + Down Arrow Key (on the keyboard)

2. Then go to Data (Tab in the ribbon) > Text to Columns.

regional dates

3. Once you select Text to columns, a dialog box appears in the window. It consists of 3 steps.

regional dates

4. In first step, Select the delimited option in the dialog box and click Next.

regional dates

5. In step 2, Since the Tab option is already selected, don’t make any changes and click Next.

regional dates

6. In step 3, Select the Date option and choose Format where the data at present shows. That is if the text is Day – Month – Year then choose DMY but if it is Month – Date – Year then choose MDY. Then click Finish.

regional dates

7. Once you click Finish, you can see that the text is converted into date format.

In the below image, you can see that the data is changed from text to date format. I have used a filter drop-down menu to show you the difference in the formatting option. Once I click the Date (Header) Filter, You can see the Date column is grouped by month and year because it has considered the column as a date format.

regional dates

Note. Since Text to columns sets the dates per the region automatically, you do not have to concentrate and changing the region/language in the Microsoft settings.

Refer to the linked articles to learn more about filter and number format options.

To know more about Excel go through our Formulas.

Do follow our YouTube channel for regular updates.

Did you know you can get your dates in week number (1 – 54) using the WEEKNUM Function in Excel? Sounds interesting? Yes, you can actually allot your dates in week numbers of the year in Excel. You can enter the dates and get the exact week number for it.

Introduction to WEEKNUM Function

You can find this option as Date/Time Function which is actually a default function in Excel. The WEEKNUM Function returns the number of the week in the entire year.

Syntax : =WEEKNUM (serial_date , [firstdayofweek])

Instance:

Consider the below image, we have mentioned a date for which we wish to calculate the WEEKNUM Function.

WEEKNUM Function

To calculate the WEEKNUM, enter the syntax =WEEKNUM(B5,2). B5 is the serial date for which we wish to calculate the Week Number. 2 represents Monday, as our weekday starts from Monday we chose that. You can choose any day as the starting day of your week as shown in the image below:

WEEKNUM Function

The result will appear as shown in the image below. IT returns 31 ans 01-08-2020 falls in the 31st week of the year.

WEEKNUM Function

Note – The serial date calculates the number of the week and since Sunday is the default for the first day of the week, the week starts with Sunday. Hence the numerical value is 1. Once you enter this syntax, the WEEKNUM gives you the day of the week in numeric value. This function is applicable only to Excel 2007, 2010, 2011, 2013, 2019 and Office 365. However, you have to be sure of the column of the serial date and the day to get the right output.

Visit our Youtube Channel for more updates.

Learn more about WEEKNUM Function at https://excelinexcel.in/ms-excel/functions/excel-weeknum-function/.

Did you know that you can actually schedule your work in adjustment to your holidays in the future? In this post, let’s learn how to use the WORKDAY function to relieve you from work trouble and have a better schedule.

Introduction to WORKDAY Function

The WORKDAY function basically provides back the date N working days of either the past or future and enables you to include or remove your work date. The WORKDAY function also includes the weekends and holidays to give you a better understanding and space for you to schedule your work. Generally, the WORKDAY function is used to get notified about the completion dates, delivery dates, submission dates, ship dates, etc. The way to use the WORKDAY function is to calculate with positive numbers for future days and negative numbers for past days.

If you are using an Excel version before the 2007 version, then you would have to initiate the Analysis toolbar, but in Excel 2007, 2010, 2013 and 2016 the WORKDAY FUNCTION comes by default. To enable this function, the following input is required,

Syntax: WORKDAY (start _date, days, [holidays])

Example on how to use the WORKDAY Function:

Let’s say you are a Project Manager in XYZ company. You wish to find the end date of your project. Do do that consider the following image. In the below image, we have mentioned the starting date, the total number of days required to complete the project. We have also mentioned the holidays. We wish to calculate the Workday i.e., the day when we will reach the completion of the project.

How to use the WORKDAY Function

To calculate the WORKDAY, enter the syntax =WORKDAY() in the cell where you want the calculated workday to appear.

There are three arguments in the syntax. The Start date, Days and Holidays. In the start date mention the starting date of the project. In the Days, mention the number of days for completion of project. And finally select the holidays range from your data.

  • For our example,
  • Select the cell where you want the output to appear.
  • Enter the Syntax. For our example the syntax is =WORKDAY(E5,E6,F6:F10). E5- Starting date, E6- Number of days for completion, F6: F10 – Holidays.
How to use the WORKDAY Function

Once you entered the Syntax, hit ENTER. The WORKDAY Function will return the estimated date of completion as shown in the below image:

How to use the WORKDAY Function

Note – The positive numbers are added in the days in the formula and to reduce days the negative number is added in days in the formula, for example,

  • Another tip on how to use the WORKDAY Function is you can also schedule the present date work using the below syntax:
  • WORKDAY ( TODAY ()  30 )
  • WORKDAY ( TODAY ()  -30 )

To know more, Visit our Youtube Channel.

To know more on how to use the WORKDAY Function, Visit Here.