Excel - 16 Features in 1 Project (Improving the User Experience)
HTML-код
- Опубликовано: 11 сен 2024
- Witness a variety of common Excel features used in a somewhat uncommon way to solicit input from the user and improve the querying and reporting process.
File Download Link:
www.bcti.com//...
The previous video showcases the typical approach:
• Excel - Overcoming "St...
02:37 Activating the Developer Ribbon
02:59 Naming the Data Set to "Sales"
03:24 Inserting Option Buttons for "Range"
06:01 Inserting a Group Box Control
06:30 Grouping Controls Together
06:46 Creating Option Buttons for Aggregation Type
08:06 Inserting A Spin Button Control
09:31 Mid-Project Recap
09:42 Creating the Sideline Calculations
09:47 Naming the User-Input
10:50 Calculating LARGE/SMALL for "N-Value"
11:34 Calculating PERCENTILE for Grouping
12:35 Creating the "Master Cutoff" Formula
13:12 Naming the "Cutoff" Result
13:20 Conditionally Format the Sales Values
15:19 Create a Dynamic Title
16:29 Create Custom Error Messages
21:24 Suppressing "Natural" Errors
21:48 Conditionally Format Error Messages
23:55 Color Match Input Cell to Error Messages
25:40 Setting a Lower Limit for User Input Using Data Validation
26:14 Creating Custom Error Messages in Data Validation
26:57 Final Touches
Another fantastic way to highlight numbers in a range/table. (TOPPIE)
A very well thought out and structured approach and easy to adapt to the own needs.
Thank you.
Looking forward for more ....
Awesome project. Masterfully explained.
Thanks a lot.
A very complete tutorial very useful for the company. Excellent work. Thank you for your time and for sharing.
I appreciate you saying so. Thanks for YOUR time, as well.
Love project videos where more than one Excel feature is covered. Looking forward to others.
@@StopWhining491 Glad you like them. I’m always worried that most people won’t watch a long-form video. Thanks for watching.
Very nice project and masterfully explained.
I personally would use the CHOOSE function instead of IF when referencing the option button linked cell output directly.
IF always has to process the test and then return the true or false option whereas CHOOSE just run the option based on the index returned by the option button.
Also CHOOSE is a charm where you have more than 2 option buttons grouped.
You are SO RIGHT! I should have done that. I just forgot about CHOOSE, and I love that little guy. I'm a dum-dum. Thanks for reminding me, and for watching.
@@bcti-bcti no way you dum, we all forget.
Keep up with what you doing, you have one of the most enjoyable Excel channels.
@@excel_along_the_way Thank you. That's high praise considering the company.
JUST AWESOME!! I took a little break from keeping up to date with the new videos and I came back to such amazing project idea! I also think this would make a great new series - oriented around creating actual projects! There is little to none of such content on youtube with Excel.
Yeah, I agree. The issue is that the amount of time it takes to put one of those "project-based" videos together, versus the number of views it generates, is just not proportional. That doesn't mean I won't still try to make those kinds of videos, they just won't occur as often as hoped for. Thanks for watching.
You won a subscriber! Thank you very much!!
@@steliostsotras9050 That YOU for subscribing. It’s always nice to see the subscriber count go up. We appreciate you taking the time to watch.
Excellent video, I'm going to pass a link to my 2 padawans in my office, so much great information.
When they take the pebble from your hand, they will be ready. (I'm mixing genres) Thanks for watching.
So cool, thank you!!
You are most welcome. We appreciate your time.
This is great! Thank you for posting it.
Thanks for taking the time to watch and say nice things!!!
Really nice! I have only two tiny changes to your solution. The first requires 365 and is the formula in the error message cell. I used:
=XLOOKUP(1,S10:S11,T10:T11,"")
Which is a tiny bit simpler and doesn't need the IFERROR function. The other thing I changed was the too high count message:
="Please select a Value less than the total number of sales entries: " & TEXT(COUNT(Sales),"#,###")
I thought providing the maximum for N would be helpful. I had to extend the merged cells (one of the few times merging is acceptable since it requires two rows) to column M to fit the message.
Still a great lesson!
100% Agreed! I prefer the XLOOKUP, but I try to make these as compatible as possible for the wider audience. Also, I thought about adding a "max count" notation, but it slipped my mind. Thanks for the reminder. Thanks for the contribution!!!
Superb video...thank you...
Thank you for saying so. I appreciate your kind words.
Poetry in motion. 😀
Thank you, loyal viewer. 😀
Like vdo
@@timestoryx Thanks. We’re glad you enjoyed it.
Create more projects
@@gagansingh3481 I’ll see what I can do. Thanks for the encouragement.