around Win XP Excel really struggled with bigger ranges to use its own "convert text to number" function and multiplying by 1 was my go to as well, but the new 365 converts itself just as quick - try it
@sawa85sa good point. The newer versions of Excel are very good at identifying numbers stored as text and enabling you to convert it with the click of a button.
I use Format Painter and custom number formats a lot with my sheets. The downside is that it's typically a once-and-done copy; you have to double-click the paintbrush icon to keep CutCopyMode on. But I prefer typing commands rather than mousing & clicking, so Paste Special Formatting is a great time saver; for a simple one-time paint Alt-H FP does the job, and for multiple paints it's a regular Ctrl-C to copy followed by Alt-H VR to paste format as often as needed.
Using copy, paste tricks a lot but today you added at least 2 new tricks links and multiplication and division through copy paste but always try to use keyboard with underlined characters for faster and smooth results, just remembering underlined characters looks tedious but still worth.
I find having "High detail" and "Expand detail" to hide and show rows and columns very handy. I group columns in most of my files, and these buttons make things very quick and easy.
Valuable! How many times have you broken me out of my 'fixed' and usually limited way to use these tools! Learning new skills never stops. Thank you a ton...
Great video , specially the last tip of copying chart formats , I used to do ctrl +d for charts , so the f4 and paste special is great for charts Paste special mainly I use the values in it , before I used also the transpose but now there is transpose formula in excel 365 Paste special is hidden gem 😀 in excel
I knew most of these but still learned a few things :) I mostly paste special values only and I always right click using the right click on the keyboard and then S, then A to do it. Didn't know about the Ctrl+Shift+V for the values only. I might use it, but I'm so used to doing it the other way. I will share that tip though as I think it's easier to remember for people who don't know.
Although most people use Paste Values, in most cases there are some columns that contain dates and they would be better off using Paste "Values and number formats". This will show the dates as dates rather than showing a 5 digit number.
Nice video, thanks. For one thing, I didn’t know the Ctrl+Alt+V shortcut for Paste Special. I have been using Alt, E, S. Probably, I will carry on using Alt, E, S, because I prefer shortcuts where you don’t have to hold two keys down at once.
Update upon Excel about number formatting in pivot tables. Since a recent update of Excel, now the number format that is used in the source tabel is automatically also used in a 'normal' pivot table. 😊 I was waiting for that a long time!!!
Yes, this is super new. Just be sure to have all your formatting in place before you build the first PivotTable, because after that, the PivotTable won't pick up changes, unlike Power Pivot.
Great video! I use the values and multiply a lot, didn't know you could use it to format charts. Just tried it and the paste special option is greyed out, does it only work in a certain versions of Excel? Super annoying as this would have been extremely hand as I need to do this quite regularly and would have saved time.
Pretty sure pasting formats for charts has been around a long time. You need to select the outer edge of the chart, then copy, then select the outer edge of the chart you want to paste it to.
Just found you channel - seems like great stuff! Will come back for more :) Do you have any video on concatenating a "formula"? Seems they removed it..?
Thank you! Not sure what you mean by 'concatenating a formula'. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hello, my question on pivot table, when double click a number in pivot table, a new sheet opens, is there any chance to have this sheet automatically formatted whenever opened ? Mainly the number format & cell size ? Thanks for your content really very helpful❤.
Hi, what would you recommend to convert data provided at 15 minutes to 10-minutes intervals? A=Datestamps at 15-minutes intervals B=data 15-minutes intervals D=Datestamps at 10-minutes intervals
Hard to say without seeing your data. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
CTRL+SHFT+V holy cow! When did they add that? I'm also embarrassed to admit I use the special paste window a lot but apparently can't read, because I never noticed the math functions. No more temp columns for me.
honestly i never thought that there will be something i wouldn’t know about in this video, but that skip blanks was new even though i use paste special like 100 times per day
They do the same thing, but they're not the same tool. If you compare the outputs, you'll see the camera tool has a black border around the image and an opaque fill, whereas the linked picture has a pale grey border and transparent background.
Its probably just me, but I use transpose enough that I memorized that menu acceleration as well. F4 is very underutilized. "Wait, how did you just do that?" Is the comment I hear.
❓ What’s the most frustrating data cleanup task you deal with in Excel?
Download the example file & cheatsheet here: bit.ly/pastespecial24file
Thank you.
These are great Mynda! I usually use ALT-E-S to bring up the Paste Special dialog box, I never new about Ctrl-Alt-V, thanks
Glad to introduce you to Ctrl-Alt-V, Chris 😁
Paste Special > Multiply by 1 is a handy way to convert numbers that came in as text values from another source.
around Win XP Excel really struggled with bigger ranges to use its own "convert text to number" function and multiplying by 1 was my go to as well, but the new 365 converts itself just as quick - try it
@sawa85sa good point. The newer versions of Excel are very good at identifying numbers stored as text and enabling you to convert it with the click of a button.
I wish I could give you more than one like because your videos are just so good! Thank you so, so much for everything you do.
I'm so happy to hear that! Thanks for watching!
I am learning much from your videos. Thanks from Pakistan.
That’s great to hear! It's always nice to know the videos are reaching across the globe.
@MyOnlineTrainingHub Thank you.
Thank you, Mynda, those are great tips from you. I'll try them all.
Awesome to hear!
I use Format Painter and custom number formats a lot with my sheets. The downside is that it's typically a once-and-done copy; you have to double-click the paintbrush icon to keep CutCopyMode on. But I prefer typing commands rather than mousing & clicking, so Paste Special Formatting is a great time saver; for a simple one-time paint Alt-H FP does the job, and for multiple paints it's a regular Ctrl-C to copy followed by Alt-H VR to paste format as often as needed.
Thank you.
Nice tips. Thanks for sharing.
Using copy, paste tricks a lot but today you added at least 2 new tricks links and multiplication and division through copy paste but always try to use keyboard with underlined characters for faster and smooth results, just remembering underlined characters looks tedious but still worth.
Thanks for sharing. Paste Link was new to me. I will use this!
Glad it was helpful!
I find having "High detail" and "Expand detail" to hide and show rows and columns very handy. I group columns in most of my files, and these buttons make things very quick and easy.
You don't use the buttons (1,2,3 etc.) above the column labels = to automatically expand and collapse the groups?
you always have the best tutorials...thank you so much
Thanks for your kind words and support!
Valuable! How many times have you broken me out of my 'fixed' and usually limited way to use these tools! Learning new skills never stops. Thank you a ton...
Awesome to hear!
Great this actually save a lot of time for my work....thanks
Awesome to hear!
Great video , specially the last tip of copying chart formats , I used to do ctrl +d for charts , so the f4 and paste special is great for charts
Paste special mainly I use the values in it , before I used also the transpose but now there is transpose formula in excel 365
Paste special is hidden gem 😀 in excel
I'm glad you found the chart formatting tip helpful! It's a real time-saver.
Thank You!
Best Wishes!
You're welcome! Thanks for watching!
Thanks, including for the cheat sheet. handy Paul
My pleasure, Paul!
I knew most of these but still learned a few things :) I mostly paste special values only and I always right click using the right click on the keyboard and then S, then A to do it. Didn't know about the Ctrl+Shift+V for the values only. I might use it, but I'm so used to doing it the other way. I will share that tip though as I think it's easier to remember for people who don't know.
That's great to hear - I'm glad you found something new!
Some great ones in here. How did you get Co-pilot into Excel? I have tried everything. I have a Excel for Enterprise, surely that includes everything?
Copilot is an extra subscription. I have it through my MVP designation, but you can purchase it.
One of the greatest windows function in my mind for copy/paste is windows clipboard, found this valuable for copying and pasting formula's and values.
Yes, I love the clipboard too. I did a video on it a while back.
Learned skip blanks in paste special today. Thanks.
So pleased you found something new!
Although most people use Paste Values, in most cases there are some columns that contain dates and they would be better off using Paste "Values and number formats". This will show the dates as dates rather than showing a 5 digit number.
Nice tip. Thanks for sharing.
Please make a video about ----all merging conditional formats------(last in first group on left/right side--which is normally dimmed)
Good suggestion 🙏 It pastes the contents and conditional formatting options from the copied cells.
Nice video, thanks. For one thing, I didn’t know the Ctrl+Alt+V shortcut for Paste Special. I have been using Alt, E, S.
Probably, I will carry on using Alt, E, S, because I prefer shortcuts where you don’t have to hold two keys down at once.
Yeah, I found the key wrangling required for CTRL+SHIFT+V hard at first, but I'm used to it now.
Update upon Excel about number formatting in pivot tables.
Since a recent update of Excel, now the number format that is used in the source tabel is automatically also used in a 'normal' pivot table.
😊 I was waiting for that a long time!!!
Yes, this is super new. Just be sure to have all your formatting in place before you build the first PivotTable, because after that, the PivotTable won't pick up changes, unlike Power Pivot.
Great video! I use the values and multiply a lot, didn't know you could use it to format charts. Just tried it and the paste special option is greyed out, does it only work in a certain versions of Excel? Super annoying as this would have been extremely hand as I need to do this quite regularly and would have saved time.
Pretty sure pasting formats for charts has been around a long time. You need to select the outer edge of the chart, then copy, then select the outer edge of the chart you want to paste it to.
@@MyOnlineTrainingHub I think it must be because they are pivot charts?
Just found you channel - seems like great stuff! Will come back for more :)
Do you have any video on concatenating a "formula"? Seems they removed it..?
Thank you! Not sure what you mean by 'concatenating a formula'. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@MyOnlineTrainingHub :) I think it was called EVAL but it's gone?
Thank you for the tip!
Very very thanks mam, 💓
Thanks for watching! 💕
Hello, my question on pivot table, when double click a number in pivot table, a new sheet opens, is there any chance to have this sheet automatically formatted whenever opened ? Mainly the number format & cell size ? Thanks for your content really very helpful❤.
No, there's no setting to bring over formatting. You'd have to write some VBA to do this.
Hi, what would you recommend to convert data provided at 15 minutes to 10-minutes intervals?
A=Datestamps at 15-minutes intervals
B=data 15-minutes intervals
D=Datestamps at 10-minutes intervals
Hard to say without seeing your data. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Great tips but if someone removes the values in your empty cells you will get errors. So once done, I always go and paste them as values.
Yes, good point 👍
Thank you
Thanks for watching!
CTRL+SHFT+V holy cow! When did they add that?
I'm also embarrassed to admit I use the special paste window a lot but apparently can't read, because I never noticed the math functions. No more temp columns for me.
😁glad you found some new things you can use.
honestly i never thought that there will be something i wouldn’t know about in this video, but that skip blanks was new even though i use paste special like 100 times per day
I was aware of skip blanks, but never understood what it did. Now I know, I am sure I will find a use for it.
So pleased to hear you both found something new!
Is the linked image actually a camera tool, by another name?
They do the same thing, but they're not the same tool. If you compare the outputs, you'll see the camera tool has a black border around the image and an opaque fill, whereas the linked picture has a pale grey border and transparent background.
I use paste special vales a lot and found the fastest way was to add it my commands on the ribbon
Got a tip on that in an upcoming video 😉
Its probably just me, but I use transpose enough that I memorized that menu acceleration as well.
F4 is very underutilized. "Wait, how did you just do that?" Is the comment I hear.
F4 is a secret weapon 😁
❤
Thanks for watching!
My pet hate is converting American, mm/dd/yyyy, to British dates, dd/mm/yyyy.
To do that use Text to Columns in Step 3 select Date and pick the original (source) date from the drop down box which in your case would MDY
Yeah, it's annoying. I use Power Query to fix dates like this on import of the data.