Did you know that you can name specific cells in your Excel workbook? Let’s say you have a list of animals in A2:A10, you can name that list “Animals”. Sounds cool, but what would you need that for? Well, if you need to reference these cells in formulas or functions and you have multiple ranges named, you can just put your name right in the formula/function instead of having to type the range or going and selecting the range. For example, you can use this for a lookup like =XLOOKUP(F1,Animals,Price) where Animals is your list of animals in A2:A10 and Price is your list of prices in B2:B10. F1 is the animal you want to go look for. If you want to learn more about XLOOKUPs, check out my guide here: https://www.excelbyashley.com/guides/xlookup. A benefit to using named ranges is you don’t have to lock your reference cells. If you had used A2:A10 in your XLOOKUP and then dragged your formula down a list of different animals, you would have to lock your reference cells to be $A$2:$A$10 otherwise it would shift to A3:A11, A4:A12, etc. as you drag it down your list. Learn more about locking reference cells here: https://www.excelbyashley.com/guides/how-to-lock-a-reference-cell-in-an-excel-formula. You can also use this in a formula like =SUMIFS(Price,Animals,"T*") where you are getting the sum of Price for anything in Animals that begins with a T. Dependent drop-down reports also rely on your ranges being named which will be covered in a later lesson. Let’s go over how to name a range. You have a few methods available. The first way is to select the cells that you want included in your named range and then click into the box in the upper left corner next to your formula bar. Typically this box shows you the cell name of what you have selected, like C5. Once you click into this box, you can type your range name and then hit enter to save. The rules are that your name needs to be one string of text without spaces and it cannot be a cell name like AA3. Once you have named your range, you can see your range name in the drop-down from that box where you set the name. You can also select the same range and you will see your range name in the box. The second way to name a range is to use your headers to your advantage with the “Create from Selection” tool. With this one, you’ll select the entire range including your header. You’ll go to Formulas in your ribbon and then click “Create from Selection.” You will get a popup asking where to pull the name from: Top Row, Left Column, Bottom Row, or Right Column. Since I have my header in the top row, I leave it on default to Top Row. This will take my top row from my selection and use that as the name of the range with the actual range of data starting in row 2. The third way you can name a range is through the Name Manager in the Formulas tab of your ribbon. Start by opening the Name Manager. Here, you will see any ranges that already exist in your workbook. This is a great tool because it allows you to edit or delete an existing range. You can also create a new range by clicking New. If you select your cells before opening the Name Manager, it helps you by detecting the possible Name and Range that you’d like to create. If you don’t have a range selected, you may select a range from the pop up or type one in. You can also type any name that you’d like along with any comments. The comments will show up in your Name Manager and may be helpful if you are sharing your document. Define Name and Use in Formula may also be helpful to you in creating your named ranges or using them in your report. Play around with it, find what works best for you, and let me know what you are using named ranges for!
0 Comments
Leave a Reply. |