How to Make a Dynamic Defined Range in Excel


Storing data in Excel simply make things easier for you or anyone who uses it. The data you store in Excel can be updated as well, enhancing its importance. Creating data every time consumes so much time, and that is why making a dynamic defined range could help you in increasing your data automatically. It can also help you in the making shorten the range of data. With the help of dynamic defined range, you don’t need to create or edit the data again from formulas, PivotTables and charts.

A dynamic defined range will do several things automatically and now let’s know more about it. To make a dynamic defined range, you need to use two formulas, which is a dynamic defined range OFFSET & INDEX.
Make a Dynamic Defined Range in Excel
To understand the process, let’s take an example and put the name of countries on the single-column list. Now to need to dynamic because if you add more name, then the range will automatically update. Without using the header cell, try the range $A$2:$A$6, but as dynamic. Now click on “Formula” bar and then “Define Name.” the next thing you need to do is in the “Name” box type name of “Countries” and now put the formula in “Refers To” box.
=$A$2:INDEX($A:$A,COUNTA($A:$A))
Type the above equation on the spreadsheet cell and copy it in a New Name box to do things faster for next time.
How does the Process of Work?
The formula in first part only specify the beginning cell of range (A2 in our term) and that the range operator is –
=$A$2:
INDEX function forced by the usage of range operator to not value a cell and returns the range instead. The INDEX function is great, and it used alongside the COUNTA function. The main function of COUNTA cells is that it counts non-blank cells in column A.
INDEX($A:$A,COUNTA($A:$A))
Formulas work on their own, and they are already settled up then you put the formula in the column. Keep the preference to save the formulas, so it will be easy for you to perform activity faster next time. The main function of INDEX is to return the range of last non-blank cell in column A ($A$6).
Because of the COUNTA function, the result is $A$2:$A$6, and now it’s dynamic and will find the final row. Now you can use “Countries” as a defined name into Data Validation rule, chart, formula and many more anything you name,  need to refer countries.
Make a Double Way Dynamic Defined Range
There are several ways that you can make modifications in height and width. In the previous example, it was only about dynamic in height; however, now we will do some extra modification and also another COUNTA function. The range can be created in various ways, but now make a range which is dynamic with both width & height. With example, it will be easy for you to understand.
  • Make a Dynamic defined range that has headers. Now tap on “Formulas” bar and then “Define Name.”
  • In the “Name” box write “Sales” and then enter the formula for the “Refers To” box.
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
  • At the beginning cell, the formula $A$1 gets in use, and then INDEX function uses a range of complete worksheet ($1:$1048576) for looking in and return form. If it starts working and COUNTS function will start to work on counting non-blank rows, and another one starts non-blank columns to make dynamic from both sides. Even though, the formula begins with A1. Now you can make dynamic chart data with just formula by using any name.

Mathew Anderson is a creative person who has been writing blogs and articles about cyber security. He writes about the latest updates regarding McAfee.com/activate and how it can improve the work experience of users. His articles have been published in many popular e-magazines, blogs and websites.

Source : Dynamic Defined Range in Excel

Comments

Post a Comment

Popular posts from this blog

Do you have VLC player? Convert Video to Audio with it

Are you looking for converting a video to audio format? Have tried it with VLC media player? It is true that the VLC media player has this feature in it to convert a video to an MP3 player. One of the main reasons for that is sometimes the songs are available only in videos MP4 format which takes space more than an MP3. So, converting a video into audio conserves space for other applications. Another reason is that who watches a music video much when you can have the audio of the song. The ability of VLC player to extract audio from video is accompanied with the several formats like AVI, WMV, 3GP, FLV, etc. There are various software products to convert a video to audio, but this tutorial is about the conversion through the VLC media player. Let’s see the steps are required in the process of conversion. How to convert a video to MP3 using VLC player Open  VLC  media player in your system. Click  Media  from the upper toolbar. Choose  Open  or Adv...
Read more

How to Change the Screensaver on your Apple TV?

Image
Apple TV is a powerful and feature-packed smart TV. You can customize your TV by adding a screensaver. If you are bored with the old screensaver, then read on and learn how to set up a new one.  Changing the Screensaver on your Apple TV If you want to change the screensaver on Apple TV, you should follow the instructions given below. First, you have to launch the Settings application on your Apple TV. After that, you should choose the General option in the settings section. Then you have to choose Screensaver option. Thereafter, you should choose “Type.” Later, you can select any option for the screensaver. Changing Screensaver Transition You can change how the screensaver transitions on your Apple TV. Here are the instructions given below. Get started by launching the Settings application on your Apple TV. After that, you should choose the General option in the settings section. Then you have to choose Screensaver. Thereafter, you should head towards Tran...
Read more

How To Change Or Update Your Email Password

Emails contain a lot of crucial information. It is recommended to keep updating the email passwords from time to time.  An ideal password consists of eight characters minimum and includes characters, symbols, numbers, and letters. A password must be strong so that anyone can’t guess it. Avoid using your personal information such as your name, date of birth, your pet’s name, etc. to protect it from cybercriminals. To change your email account’s password, follow these simple steps: Changing Your Gmail Password Go to Gmail login page. Enter your account details like Gmail ID and password to access your account. Note that you can’t reset your password through the mobile application. On your Gmail account page, go to “Settings” and open the tab. Now tap on the “Accounts and Import.” Hit on “Change password” link option. Here you have to type your active Gmail password, and then in the next field, you have to enter the new one. The system will ask you to pr...
Read more