We have additional free Excel training here 👉 ruclips.net/p/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI and here 👉 ruclips.net/p/PLzj7TwUeMQ3gu_cJg5cV8RDdBNUVOvG5u and Excel Tips and Tricks Training 👉 ruclips.net/p/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc 🔥Check out our FREE 300+ hour training course playlist here 👉ruclips.net/p/PLzj7TwUeMQ3hsADWJd2sJh4o8jATTyaRU
Thank you so much!! Really helpful and handy those tricks are... But if I Wanna change the default weekend from sat & sun to Fri& sat , how can do that? Thanks in advance
Let's assume that you want to make a series of January 2023 dates (you can adapt it to your needs of course): 1. Enter in A1: 1/1/2023 2. Drag the fill handle down to cell A31 to enter successive dates until A31: 31/1/2023 3. Highlight the range A1:A31 and 4. Highlight yellow all Fridays and Saturdays by selecting: Home-Styles/Conditional Formatting - New Rule.... and enter Format values where this formula is true: "=WEEKDAY($A1)>5" (without quotes) and select Format-Fill-Yellow-OK. 5. Use the free "ASAP Utilities" (make a google search)and install: Select ASAP Utilities-Select...- 1. Select Cells based on content, formatting and more.., In the pop up window select: Cell color, A6, Range (push-button) Range: A1:A31, Cell (push-button), Auto close after selecting, OK 6. In Excel, select Home-Delete-Delete Cells...-Shift Cells up (push-button) 7. Copy the Range A1:A26 (containing your workdays only) and paste wherever you want. Of course there is another way by using VBA but it is more complicated, I believe, 🙂 Another way is: 1. Enter in A1: "Date" and in B1: "WeekDay" 2. Enter in A2: 1/1/2023 and in B2: =WEEKDAY(A2) 3. Select Region A2:B2 and drag the fill handle down to cell A32 to enter successive dates until A31: 31/1/2023 4. Make it a Table: on A2 press Ctrl-T 5. Inside the table press CTRL-SHIFT-L to apply Filter 6. Filter row WeekDay by selecting ONLY 6 and 7 7. Delete all shown rows 8. Remove filter 9. Delete column B 10. Copy the Range A2:A27 (containing your workdays only) and paste wherever you want.
6:05 7:15 Ctrl A to select everything in the worksheet 7:54 rows 9:00 10:10 11:30 12:32 flash fill Ctrl e 13:24 All Shortcuts keys... Alt key 13:58 take the data out of the table 14:20 convert to range... Ctrl T 15:20 Sum multiple items from multiple tables 15:50 auto sum keyboard shortcut which is equals 16:15 17:12 19:55 Quick way to create a table Ctrl T 20:45 table names 24:50 quick access tool box 26:00 more commands 29:50 Fill series 31:10 32:00 34:00 Developer ribbon 34:45 control group for to-do lists 37:05 create lots of folders 39:50 Ctrl shift Plus... to add rows and columns 48:10 53:20 rept function 54:50 sparkline 56:30
@30:00 Fill Series Numbers, I would use another faster method: METHOD 1: Enter 1 in A1, enter 2 in A2, Mark the range A1:A2 and double click on the fill handle or even faster: METHOD 2: Enter 1 in A1, Mark cell A1 and HOLDING DOWN CTRL key drag on A2 to automatically enter 2 You can keep dragging (holding the CTRL key !!!) all he way down OR double click on the fill handle as above method 1 - For me sequence is not suggested unless you copy-paste the value on the same column (No.) Another shortcut that 99% of XL-users do not know (yet) is scrolling horizontally with CTRL-SHIFT-mouse wheel (XL 2021+365). For horizontal scrolling in older versions: Middle Click + move mouse Right/Left (or up/down for vertical scrolling) (the farther you move, the faster is the scrolling) REMINDER: mouse wheel = vertical scrolling and CTRL+mouse wheel = Zoom (in all XL versions) 😀
“Quick Time Check” hack is invaluable to create templates to copy and paste into SAP for stocktake counts/adjustments etc due to the gap setup in SAP. 🎉
We have additional free Excel training here 👉 ruclips.net/p/PLzj7TwUeMQ3g6U7Mwyy7G9Gwh-k2yNtfI and here 👉 ruclips.net/p/PLzj7TwUeMQ3gu_cJg5cV8RDdBNUVOvG5u and Excel Tips and Tricks Training 👉 ruclips.net/p/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc
🔥Check out our FREE 300+ hour training course playlist here 👉ruclips.net/p/PLzj7TwUeMQ3hsADWJd2sJh4o8jATTyaRU
thank you so much for this video, I'm being addicted to watching all your tutorials Madam Ashby... more power to you.
Thank you for the webinar. We need more of such.
More to come! And thank you for your support. 😊
Most informative channel ever found in RUclips 🎉
Appreciate that.
Thanks a lot Deborah for such value added video....Stay blessed
Very helpful video. I appreciate you for sharing and teaching.
Thank you for your very informational presentation...
Thanks for tutorial.. It'is very useful..
You're the best. TY
😀
Thanks for this excellent video!👍
Massive respect ! Thanks a lot !💙💙💙
Appreciate that, Zeidulla Mammadov :-)
Thanks
Thank you so much!! Really helpful and handy those tricks are...
But if I Wanna change the default weekend from sat & sun to Fri& sat , how can do that?
Thanks in advance
Let's assume that you want to make a series of January 2023 dates (you can adapt it to your needs of course):
1. Enter in A1: 1/1/2023
2. Drag the fill handle down to cell A31 to enter successive dates until A31: 31/1/2023
3. Highlight the range A1:A31 and
4. Highlight yellow all Fridays and Saturdays by selecting: Home-Styles/Conditional Formatting - New Rule.... and enter Format values where this formula is true: "=WEEKDAY($A1)>5" (without quotes) and select Format-Fill-Yellow-OK.
5. Use the free "ASAP Utilities" (make a google search)and install:
Select ASAP Utilities-Select...- 1. Select Cells based on content, formatting and more..,
In the pop up window select: Cell color, A6, Range (push-button) Range: A1:A31, Cell (push-button), Auto close after selecting, OK
6. In Excel, select Home-Delete-Delete Cells...-Shift Cells up (push-button)
7. Copy the Range A1:A26 (containing your workdays only) and paste wherever you want.
Of course there is another way by using VBA but it is more complicated, I believe, 🙂
Another way is:
1. Enter in A1: "Date" and in B1: "WeekDay"
2. Enter in A2: 1/1/2023 and in B2: =WEEKDAY(A2)
3. Select Region A2:B2 and drag the fill handle down to cell A32 to enter successive dates until A31: 31/1/2023
4. Make it a Table: on A2 press Ctrl-T
5. Inside the table press CTRL-SHIFT-L to apply Filter
6. Filter row WeekDay by selecting ONLY 6 and 7
7. Delete all shown rows
8. Remove filter
9. Delete column B
10. Copy the Range A2:A27 (containing your workdays only) and paste wherever you want.
Hi, a good one. A question: In auto fill if there is no street number, what is the behavior?
thanks a lot!
Glad it was helpful! 🤩 Learn more Microsoft Excel Tips and Tricks here ➡️
ruclips.net/p/PLzj7TwUeMQ3gv7nwM6ad7r5ma_3UoOPtc
it was perfect
6:05
7:15 Ctrl A to select everything in the worksheet
7:54 rows
9:00
10:10
11:30
12:32 flash fill Ctrl e
13:24 All Shortcuts keys... Alt key
13:58 take the data out of the table
14:20 convert to range... Ctrl T
15:20 Sum multiple items from multiple tables
15:50 auto sum keyboard shortcut which is equals
16:15
17:12
19:55 Quick way to create a table Ctrl T
20:45 table names
24:50 quick access tool box
26:00 more commands
29:50 Fill series
31:10
32:00
34:00 Developer ribbon
34:45 control group for to-do lists
37:05 create lots of folders
39:50 Ctrl shift Plus... to add rows and columns
48:10
53:20 rept function
54:50 sparkline
56:30
@30:00 Fill Series Numbers, I would use another faster method:
METHOD 1: Enter 1 in A1, enter 2 in A2, Mark the range A1:A2 and double click on the fill handle
or even faster:
METHOD 2: Enter 1 in A1, Mark cell A1 and HOLDING DOWN CTRL key drag on A2 to automatically enter 2
You can keep dragging (holding the CTRL key !!!) all he way down OR double click on the fill handle as above method 1
- For me sequence is not suggested unless you copy-paste the value on the same column (No.)
Another shortcut that 99% of XL-users do not know (yet) is scrolling horizontally with CTRL-SHIFT-mouse wheel (XL 2021+365).
For horizontal scrolling in older versions: Middle Click + move mouse Right/Left (or up/down for vertical scrolling) (the farther you move, the faster is the scrolling)
REMINDER: mouse wheel = vertical scrolling and CTRL+mouse wheel = Zoom (in all XL versions)
😀
Great suggestions, thank you! 😀
“Quick Time Check” hack is invaluable to create templates to copy and paste into SAP for stocktake counts/adjustments etc due to the gap setup in SAP. 🎉
Thanks for the kind comments 🙂
Thanks