![Premier Training Hub](/img/default-banner.jpg)
- Видео 83
- Просмотров 183 768
Premier Training Hub
Добавлен 19 июл 2022
Premier Training Hub is started by Ankur Gupta, who is a CFA Charterholder (USA) and an MBA from one of the prestigious colleges in India. Ankur has a corporate experience of 13+ years as a Financial Strategist and a Data Expert and has a stronghold over a lot of Academic & Professional Tools, Microsoft Excel being his favourite tool. He believes a lot of what you can achieve with Excel is never taught in college or ever understood in professional working environement, and the channel aims to cover all such things in every minute detail, with its videos. So stay tuned, discover your inquisitive side and be a part of this insightful journey with him.
Top Latest & Exciting New Feature Additions to Microsoft Excel (2023)
With every feature update, Excel is becoming better than ever. There is so much today, that we can do in excel which at one time seemed impossible. This video will cover the latest excel features that Microsoft added in the previous few months and how they have been a game change for excel users.
Contents:
0:00 Whats inside!
0:19 The new search box for Excel Menu
1:22 Copy arithmetic data from status bar
2:44 Check formulas with value tooltip
3:40 New Keyboard Shortcut to paste values
4:32 Load dynamic arrays to power query
7:48 New keyboard shortcut for power query
8:14 Create your own nested data types
11:31 The best AI Tool / Analyse tab
12:54 Share a section of the workbook
Watch the best excel ...
Contents:
0:00 Whats inside!
0:19 The new search box for Excel Menu
1:22 Copy arithmetic data from status bar
2:44 Check formulas with value tooltip
3:40 New Keyboard Shortcut to paste values
4:32 Load dynamic arrays to power query
7:48 New keyboard shortcut for power query
8:14 Create your own nested data types
11:31 The best AI Tool / Analyse tab
12:54 Share a section of the workbook
Watch the best excel ...
Просмотров: 578
Видео
Excel’s Watch Window: A Must know feature for Large Files & Complex Financial Models
Просмотров 450Год назад
If you are working on a large file containing several sheets, and some of those sheets contain various inputs, while some sheets contain formulas or output values and you want to track all outputs together at one single place, then you have to use Excel Watch window. Its super easy to apply and you can see all changes to your cells and sheets, dynamically in a single window that can be accessed...
Create NLOOKUP () function to find n'th occurrence of lookup value (something even XLOOKUP cant do)
Просмотров 1,2 тыс.Год назад
We now have the Xlookup function in Excel 365 which is great replacement for Vlookup. But there is one thing which I believe should have been added to the Xlookup function i.e. return output for the nth occurrence of a lookup value. But thanks to excel, this can still be done using the new lambda function. So lets create our own Nlookup function to take care of this problem. Timestamps: 0:00 Wh...
Learn to create and manipulate ‘3D Named Ranges’ in Excel (Includes a Bonus VBA Code)
Просмотров 372Год назад
A named range usually takes input from one single sheet, but excel provides you the option to create a 3D Named Range as well, which takes input from multiple sheets and perform required operations on the collected data. A 3D Named Range is particularly useful in scenarios where you have multiple data points for a single item, in multiple sheets, and you want to add all data points. For ex, you...
Learn the best methods to create 3 types of Waffle Charts in Excel
Просмотров 150Год назад
Bored of using the same charts again and again in excel? Me too. That’s why I am sharing my analysis on how to create a waffle chart using 3 innovative excel techniques. With this video, you will learn all 3 techniques in detail and understand the pros and cons of each technique, and also when to use either of these techniques. This video will also provide a VBA technique to create this chart, ...
10 Practical Uses of Excel’s Unique () Function
Просмотров 372Год назад
Unique function works quite well with the data cleaning functions like TRIM () & CLEAN () in excel. It is also a great match for FILTER () & SORT () functions. In this video, we are going to practice 10 different examples, where we will cover the basics and advanced uses of this function and also we will see how to combine UNIQUE function along with several other important functions in excel, a...
Powerful Pivot Trick: Join multiple sheets with Excel Query Connections & create unique pivot table
Просмотров 628Год назад
Lets learn another useful and advanced pivot table trick in this video. Combining data from several files into one file is not the same as combining data from several sheets, all of which are in a single file. To create a pivot table from multiple sheets in a single file, you have to create query connections in Power Query. Now this is very easy to do and involves just 2 steps. Even if you don’...
The underrated Hyperlink Function in Excel: Combine with a simple VBA code to unleash its full power
Просмотров 586Год назад
Do you use Hyperlink Function in Excel? Well, I use it quite often in my reports because it enables me to present data in a very simple and easy to read manner. Also, it does something which no other formula in excel can do. It can detect your mouse location, and perform operation without clicking on any cell or pressing enter. Confused? Well don’t be, just watch the video and learn . Download ...
Excel Flash Fill Automation Trick: Create Appraisal Letters for entire company in 1 minute
Просмотров 234Год назад
There are a lot of inbuilt Artificial Intelligence / AI Tools in Excel. Flash Fill is one of these tools and this video will tell you how can you automate a lengthy, boring and routine office task and get it done quickly. We will learn how to write, customise and print letters for every employee of a company (size of the company does not matter) and achieve the same with lightening speed.
Sequence Function: Create Sequential List of Dates, Weekdays & Months in Excel with Best Examples
Просмотров 2,1 тыс.Год назад
There are 3 ways to create sequential lists in excel (Control Drag, Fill Series & Sequence Function). All of them are great but Sequence function provides the added advantage of being more user friendly and makes it easier to edit your list, if needed. Also, its a latest function and you must learn it. Lets cover two examples, where we will learn how to combine Sequence Function with other func...
TAKE () Function in Excel (Learn its real use & how to combine it with other functions in excel)
Просмотров 524Год назад
Why did excel create TAKE function in Microsoft 365 ! Lets figure out the exact need of this function and its real use with an example. In the process, we will learn how to combine it with other functions in excel like SORT (), FILTER () to achieve desired analysis output.
Correct Wrong Totals for Filtered Items in Pivot Tables (Use Filtered Items with Excel Data Model)
Просмотров 2,3 тыс.Год назад
Lets get rid of one more pivot table related issue in this video. The default calculations in pivot tables ignore all those items which are filtered out from the Row Labels. If you filter out one item, the result for other items can get modified incorrectly without your consent. If left unnoticed, this can create wrong results. Lets have a look at this problem in detail in this video and find a...
DGET () & DSUM () Function: Overcome limitations of VLOOKUP & SUMIFS with database function in Excel
Просмотров 965Год назад
Its been more than 15 years since Excel introduced the Database Functions. Then why don’t we use them? Some of the frequent problems faced with commonly used functions like Vlookup & Sumifs can be solved easily with the help of database functions. In this video, we will explore those frequent problem scenarios and how we can replace other functions with database functions to find easy solutions...
The Least Known Excel Trick: Use Hash sign (#) to create Unique & Dynamic Drop Down Lists
Просмотров 481Год назад
Its 2023 and you have to keep yourself updated with all new functions, formulas and techniques Excel is offering you. Lets learn a must know, but at the same time very little known, data validation hack in this short video.
Build Stock Management Tool with Fully Automated Sales, Purchase and Stock Register (in Excel)
Просмотров 268Год назад
Today is the world of automation and there are a lot of analytical softwares available in the market. But still Microsoft Excel remains the most used analytical tool across the world. Its important that we know everything that can be done in the field of automation using Excel. Lets focus on one such example and create a fully automated stock management tool in excel by utilising excel’s unique...
Create Attractive Doughnut / Pie Charts for Presentations & Dashboards
Просмотров 123Год назад
Create Attractive Doughnut / Pie Charts for Presentations & Dashboards
The Latest Pivot Table Advanced Trick (Create 2 timelines on a single pivot table)
Просмотров 944Год назад
The Latest Pivot Table Advanced Trick (Create 2 timelines on a single pivot table)
10 Cool Chart Tricks in Excel to build creative Dashboards & Presentations
Просмотров 570Год назад
10 Cool Chart Tricks in Excel to build creative Dashboards & Presentations
The New ‘Expand’ Function in Excel | Create Datasets Quickly with Expand Function | Best Explanation
Просмотров 3,9 тыс.Год назад
The New ‘Expand’ Function in Excel | Create Datasets Quickly with Expand Function | Best Explanation
Exclusive Charts in Google Sheets: Organisational Chart & Gauge Chart/ Speedometer Chart
Просмотров 253Год назад
Exclusive Charts in Google Sheets: Organisational Chart & Gauge Chart/ Speedometer Chart
Pivot Table Printing Hack: Organise & Print Large Data Systematically using ‘Item wise Page Breaks’
Просмотров 2,4 тыс.Год назад
Pivot Table Printing Hack: Organise & Print Large Data Systematically using ‘Item wise Page Breaks’
The Inbuilt Data Consolidation Tool in Excel: Combine Multiple Reports into One Single Report
Просмотров 1 тыс.Год назад
The Inbuilt Data Consolidation Tool in Excel: Combine Multiple Reports into One Single Report
Learn all 3 methods of Recording Macros in Excel (Absolute, Relative, By Selection) with Examples.
Просмотров 860Год назад
Learn all 3 methods of Recording Macros in Excel (Absolute, Relative, By Selection) with Examples.
10 Essential Time Saving Tips for Microsoft Excel
Просмотров 981Год назад
10 Essential Time Saving Tips for Microsoft Excel
Dynamic Excel Charts Trick: Add Automated Data Labels to any Chart
Просмотров 2,4 тыс.Год назад
Dynamic Excel Charts Trick: Add Automated Data Labels to any Chart
Learn these Hidden Features to filter Dates in Excel Pivot Tables
Просмотров 1,1 тыс.Год назад
Learn these Hidden Features to filter Dates in Excel Pivot Tables
Learn to use 'Solve Order' to correct unwanted mistakes in Pivot Table Calculated Fields
Просмотров 1,1 тыс.Год назад
Learn to use 'Solve Order' to correct unwanted mistakes in Pivot Table Calculated Fields
Use ChatGPT to create a Gauge Chart / Speedometer Chart in Excel
Просмотров 1,1 тыс.Год назад
Use ChatGPT to create a Gauge Chart / Speedometer Chart in Excel
Learn how to manipulate dates with the important date functions in Excel
Просмотров 636Год назад
Learn how to manipulate dates with the important date functions in Excel
Best way to create the Sales & Margin Growth Chart in Excel (4 charts combined into 1 chart)
Просмотров 19 тыс.Год назад
Best way to create the Sales & Margin Growth Chart in Excel (4 charts combined into 1 chart)
how to do the same thing in mac? because this option is not showing on my mac
Very good examples!
Awesome..Thanks
can you share the link for the chart video mentioned at the end of video?
Well explained
Tq
Thank you so much!!
The best explanation about expand function ever. Thank you
This absolutely worth watching n learning new things in excel. I watched your couple of videos and learnt new things. Great JOB. I am you fan now.
This is first ever instance where I learnt something new, Thank you.
Really I liked your videos... Thanks for your time to post this..❤
Can we do this in PowerPoint?
Hi. I have a query: When I Convert file excel (From other sources), Table import wizard noticed that "0 Rows transferred". How can I fix it?. Although I checked my file again, and it actives nomarlly (it have 422 rows)
Nice. Please give the files
Thanks a lot sir 🙏
Hi Sir, Thank You for sharing a very interesting video on EXPAND 🙏🏾 Sir, what if the Products are: Belt Goggles Camera The final Product list should have the items in the same order (Belt, Goggles, Camera). The solution in the video will sort the item in A-Z order. Please suggest a solution. Thank You!
So far best video on these three pivot table topics!
Very good info! Thanks for sharing!
very helpful thanks!
no file for down load
Thanks a Lot!
INCREDIBLY Helpful!
thank you man
Great very helpful
clear instruction, thank you, so basically excel doesn't have bi-directional, mirror chart or wtf it is
Nice one.. Thanks a lot
But what if I want this letter on my company letterhead and also download this as a pdf for all these employees and use mail merge to send this?
Hi! what if i wish to combine multiple workbooks with the exactly sale headers? Please advise
❤😂
Great 👍🏼. Is there a way to apply the same concept and eliminate FRIDAYS from days between tow dated ?!
Superb!! waiting more the practical video like this
Great straightforward video, thanks!
Thanks Teacher
When I select 1st series, instead of highlighting a column, it highlights the entire row including a, b, c, d, e and f
You can achieve the same result by using a very short Syntax as below and then if needed, convert that into a lambda function with 4 input parameters as defined below the formula =INDEX(FILTER($B$3:$B$16,$A$3:$A$16=D3),E3) $B$3:$B$16 is the item names; in your case--> fruit names $A$3:$A$16 is the dates D3 is the individual item you want to evaluate E3 is the Nth value you wish to obtain In order to avoid errors on selection of the Nth value you can use the formula =SEQUENCE(COUNTIF($A$3:$A$16,D3)) and reference the above to a Drop Down list Once done, user can only select the numbers from the drop down
Can we get sequence of quarter date between two input dates? i.e., If I put 15 Jan. 2024 and 15 Dec. 2024 then it suppose to display 15 Jan. 2024, 15 Mar. 2024, 15 June, 2024, 15 Sep. 2024 and 15 Dec. 2024. Thanks in advance
Worked perfect! Thanks man
Thanks a lot for the video. I have made similar output with much more work the last many years. Now I have just to figure out how to expand with 3 or 4 tables. If anybody have an Expand formula ready using 3 or 4 tables, I will very much appreciate to see it.
Thanks a lot PTH - it's really an elegant solution to expand items in a list to a dataset. Just had to practice a bit to expand formulas to use 3 tables. This is going to save me a lot of time.
Your Excel tutorials are fantastic, and I encourage you to keep up the excellent work! Your expertise and engaging teaching style make complex topics understandable. The practical insights you provide are invaluable. I look forward to more of your content, as it continues to be a tremendous asset for learners. Thank you for your dedication! 👏🚀
Your professional-level Excel tutorials are exceptional! Your expertise in breaking down complex features, paired with real-world applications, has significantly enhanced my skills. The commitment to high production standards amplifies the learning experience. Thank you for being a valuable resource for advanced Excel knowledge - your contributions are truly appreciated. 👏🚀
Thanks for your appreciation
I feel there is a problem with the horizontal axis labeling. center should be 0 and then scaling should run both ways.
Thats not a problem for the output we are looking for. You do not need to show the horizontal axis labeling as the values are depicted by data labels. So just hide the axis as there is no need to put axis on your chart or presentation.
@@premiertraininghubif I want to stack sales and margin in one bar chart(clustered column?) and stack sales% and margin % in another clustered column. Basically side by side stacking Sales and Margin/ Sales% and margin%, how do I do that? When I selected stacked column for Sales and Margin under ‘Chart Type’, all changed to stacked column. Please help. Thank you!
😁 Thanks for that solution concerning Pivot Tables that I'm looking for since long time...
Hello Sir, your videos are very helpful, do you provide personal trainings or can you provide practise files of all the videos?
Excellent practical examples- thank you! I have found myself using unique a lot & would not have thought of your solutions.
You're very welcome!
Great
Great video! Here is an easier to use formula to accomplish the job: =TOCOL(Table1[Product]&EXPAND("",,ROWS(Table2[month]),""))
Thanks for liking the video. Thats also a nice way of accompalishing the task but whether it is easier or not is a perception :). There is one thing that you missed to mention in your solution, so let me add the same for the benefit of readers. In the method advised by you, for creating the 2nd column (Month column) in the output database, we would need to modify the TOCOL formula by adding a 3rd argument as "1", else the formula would create an incorrect database (this is in addition to reversing the Tables as shown in the video). The formula for the month column in the formulas advised by you would become :- =TOCOL(Table3[Month]&EXPAND("",,ROWS(Table1[Product]),""),,1) But i am not sure whether appending a blank array with a table would be intuitive for many people.
awesome. i was looking for it this morning and found out at afternoon without looking for it.
thank you, does this apply to text fields as well?
kindly do share the excel sheet
I love you dude, thank you so much!
Glad I could help!