Breaking news from around the world
Get the Bing + MSN extension
Now Available in Community - MBAS 2019 Presentation Videos
Catch the most popular sessions on demand and learn how Dynamics 365, Power BI, PowerApps, Microsoft Flow, and Excel are powering major transformations around the globe. | View Gallery
2019 release wave 2 Discover the latest updates to Dynamics 365Release overview guides and videos Release Plan | Early Access Availability
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Talent TechTalks | Upcoming TechTalks
Today’s #TipTuesday post is a little Excel tip. It’s one of those things that I assume most people know it, and then I work wth someone and watch them use Excel and end up teaching it to someone. I call it corner copy but I’m sure there’s some official name!
There are 2 parts to this tip really: one is the copy via double clicking on the lower right corner and the bigger tip is the little pop-up window that gives you options on how that command is executed. That little pop-up window is the *gem* of this tip in my opinion as it appears in numerous instances of doing things in Excel.
If you want to copy values down a column, there are several ways to achieve this.
Here’s an example. I have a value I want to *copy* to other rows in this column. I hover over the bottom right corner until the cursor changes to a different plus sign icon (+). Now double click. Because I have adjacent rows on the left side, it copies down until there is a blank in my data. It’s wonderful!
However, I’m copying a number and it thinks it’s a series, not a copy function. What the heck? Don’t freak out, there’s a magic button that will help you here. Here is what you might find with dates, numbers and similar data types if you do this:
At the bottom of the curren *screen* (not the end where the data was copied to). you will find a little magic button with options. This doesn’t stay visible for long so if you make the mistake of scrolling down to see that the copy went all the way to the bottom of your dataset, you will be out of luck here. Trust me, once you see the data is in series and should be copy (or vice versa), click on this little button first.
Above is an example of what you might see. The options will differ depending on the type of data you’re working with. In my case, it guessed I wanted to see a Series of data but I wanted a Copy instead so I simply need to select Copy Cells and all of the values it created are now a copy.
The other useful features in this pop-up are the Fill with and without formatting. I use Fill Without Formatting surprisingly often. It seems I work a lot with tables where rows are formatted with special shading, borders or other features so a copy overwrites those. In those cases, I can’t pick both options so if the “copy” part did what I want, I can use Fill Without Formattng to just copy the formulas down, for instance.
All in all I love this as it’s so much faster that the alternative options. Most times I find I am working with data that is in contiguous cells therefore the “adjacent cells” rule works and the copy is successful. If you’re often copying where you have blank rows, this won’t be as successful.
As I mentioned above, it copies down to adjacent rows until it finds no data. I have found inconsistent behaviour at times when there is data in the column already and to be completely honest, I’m not sure what the proper default behaviour should be.
Example: I’m copying from row 5 in column C. I have data in that column in row 20. Sometimes when I do the double click it will copy and stop at row 19, recognizing there’s data in row 20. Sometimes it copies right over it. I’m sure the answer is in some cases I’ve had cell values and in others it might have been a formula, I’m not sure. Test on your own data to be sure you are getting the results you expect!
Business Applications communities