# Symbol in Dynamic Arrays

FPNA guy here. Yesterday I put out my dynamic arrays guidebook and one of the questions I had in the comments is could you explain more the pound sign with dynamic arrays? So I thought I would record a quick video this morning to give you a little bit of insight into the pound sign for dynamic arrays. So I have here an Excel sheet and what we're going to do is we're going to a sequence formula. So this is a dynamic formula. We're going to start with that. And what I'm going to do is I'm going to do. So we're going to do end of month and then we're going to put in our date. So we have a start date. We're going to say it's 20231 1. You can see it here. Now I'm going to sequence my months and so what I'm going to say is I want a year, So I want 12 months for this. I want one column. I'm going to start with zero, which means I'm going to start with 20231 1. And I'm going to increase by one month each time. So we're going to click that and I forgot to put the second parenthesis. So it will do it for me. Put yes, we're going to change this to a date format. So if I come up here and hit short date, what we're going to see is you can see this is dynamic. You can see the entire box around it. You'll notice the formula is grayed out in all the other cells. So what I want to show you, let's say I wanted to reference this formula elsewhere. So right now I have it in rows. Let's just say an example. I want to put it on another sheet in columns here. So I want to use it here. What I could do is I could say equals transpose because we want it in columns. And I come back to my arrays here and I highlight this and notice when I highlighted it, it added a pound at the end. Let's say I select the first 11 there. It doesn't do a pound. If I select the first three, it's not a pound. It's only doing it for the entire range of the dynamic formula. So when I hit it, hit that. You could see I have the dates here. You'll notice the formatting doesn't carry over. So I'll update that. So what the pound tells Excel is, hey, that's a dynamic formula. And now I'm going to show you why that's important. So let's go back here and let's Change this to 24 months. Right? So we have 24. We want to change our formatting. So we'll just copy that real quick. Do that. Let's go back to our other sheet and see what happened. Notice it now has 24 months. If I clean up the formatting here, make sure that's all a date, short date. You can see it now has 24 months. That's the benefit of the pound. If I had done transpose and let's just say I referenced the first 12, right, then I only get 12. So the pound says take whatever the formula is and adjust. I'll show one other example. We're going to do a simple sequence here. We're going to start with seven rows or seven. We'll do actually one row, seven columns. We're going to start with zero. We're going to increment by one. So you can see it goes from zero to six. Now let's say I do the reverse of that and do the reverse here. We're gonna again do one row, seven columns. We're gonna start with seven, negative one. So you can see it goes down to there. Now what you're gonna notice is let's say I highlight this, I want to add them together. Notice the formula does pound and pound. So again, if I was to change this and say 8 here, the formula adjusts, but it gives an error because there's no number here. But if I change this one to also, you know, be eight. So we want eight columns. Now the formula updates versus let's just say I have the number 1 2, 312332, 1. And I decided to do it as one formula. Dynamically, I highlight this. I highlight this. Notice here, here there's no pound. So if I add a number to this range, it will not dynamically get added. If I put a four here, say a zero here and a zero here, I would have to go back and rewrite the formula. So now I'd have to do this plus this. And notice I get a spill error because there was already something there and I have to delete that and now it works. So that's really what the pound symbol is for. Is whenever you're using dynamic formulas, it's telling you to reference the entire range. I also just want to share a couple resources for those who'd like to learn more about dynamic arrays. First, if you go to my YouTube channel, you'll see I have five videos today and I'll add more over time. About dynamic arrays removing duplicates, the unique function xlookup, these other two and this one here I'll add today. And then next I want to share two other things with you today. Yesterday I published my Excel Dynamic Arrays guide. You can download that under my FP and a guide website. You can also go to LinkedIn. You'll find it when you look at my recent posts and it will include information about dynamic arrays and the different formulas for for dynamic arrays. So you can do that. And then the last thing I want to share is I'm teaching. Actually, I'm not teaching, but I'm helping coordinate and promote a course with Abbott Katz. He is the author of Up, up and Array. So for only $99, which is just an absolutely phenomenal deal, he is going to provide six hours of training on 20 dynamic formulas. And what I can promise you is you'll get your money out that will change the way you work in Excel if you're not using them. So I hope you enjoyed this video and learning a little bit more about dynamic arrays. And I'm grateful for the question today I had on hey, what's the pound sign for? How does it work? Why should we use it? So looking forward to my next video with you and thanks for watching this.