Excel 365: Awesome new functions

[Neeraj Kaushik]

Dear Friends

Do you spend hours on Excel and Word documents? What if you could access them for free, with even more powerful features?

Scholars can get a completely free account on https://m365.cloud.microsoft/ using their academic email address (Regret Gmail users). This unlocks the online versions of Word, Excel, and PowerPoint, giving you access to all the tools you need for your studies. Whether you're working on a research paper in Word or analyzing data for a project in Excel, you can now do it from any computer, anywhere, without needing to install any software. 

For all the Excel users, you can now enhance your productivity with new functions and advanced formulas that are exclusive to the web version. This is the perfect way to streamline your workflow and take your academic work to the next level, all at no cost.

I've made some videos on the same. Let's begin with creating an free account and have a look on the interface:

Office-365 Part-1 Create an Account: https://youtu.be/drea7Kkv8SQ

Happy Learning

Neeraj 

[Neeraj Kaushik]

Dear Friends

Are you ready to supercharge your data cleaning and organization skills? 

In the second video, we're diving deep into 4 new text functions that will completely change the way we work with text data in Excel. We'll explore powerful functions like TEXTJOIN, TEXTSPLIT, TEXTBEFORE, and TEXTAFTER, showing how to combine, split, and extract text with incredible precision and efficiency. 

Office-365 Part-2 New Text Functions- TEXTJOIN, TEXTSPLIT, TEXTBEFORE, and TEXTAFTER: https://youtu.be/AN_kWGjbntI

Happy Learning

Neeraj

[Neeraj Kaushik]

Dear Friends

The Lookup Family of Functions in Excel are dynamic array functions that help us reorganize and manipulate data with ease. These functions, which were introduced in Microsoft 365, provide powerful tools for reshaping your data, making them incredibly useful for data analysis and reporting.

1. HSTACK: Joins multiple arrays or ranges of data side-by-side to create one larger array.
2. VSTACK: Appends multiple arrays or ranges one on top of the other to create a single, vertical array.
3. CHOOSECOLS: Extracts specific columns from an array or range based on their column numbers.
4. CHOOSEROWS: Extracts specific rows from an array or range based on their row numbers.
5. TAKE: Returns a specified number of rows or columns from the start or end of an array.
6. DROP: Removes a specified number of rows or columns from the start or end of an array.
7. TOCOL: Transforms an array into a single column by reading the data row by row.
8. TOROW: Transforms an array into a single row by reading the data column by column.
9. TRANSPOSE: Flips the orientation of an array, converting rows into columns and columns into rows.
10. UNIQUE: Returns a list of unique values from a range or array, removing any duplicates.

I've explained all these in this video:

Office-365 Part-3 Additional lookup functions: https://youtu.be/komk5XciZMk

Happy Learning

Neeraj

[Neeraj Kaushik]

Dear Friends

The XLOOKUP function in Excel is a versatile and modern tool for performing lookups. We can use it to search for a value in a column or row and return a corresponding value from another column or row, regardless of its position. It's a significant improvement over older functions like VLOOKUP and HLOOKUP or INDEX+.MATCH. We can perform exact and approximate matches, use wildcards, and even search from the bottom up. This function simplifies complex lookups and gives us greater control over search behavior.

I've explained the same in this video:

Office-365 Part-4 xlookup: https://youtu.be/Ii6rTn0eHqc

Happy Learning

Neeraj

[Neeraj Kaushik]

Dear Friends,

My last video about xlookup was removed by YouTube due to the inclusion of email IDs and phone numbers (even though all were dummy data), which violated their community guidelines.

So I have re-recorded the video, ensuring that all sensitive information, even if dummy, has been removed to comply with YouTube's policies. Regers for any inconvenience.

Office-365 Part-4 xlookup: https://youtu.be/fVZCPxcfxRg

Happy Learning

Neeraj

[Neeraj Kaushik]

Dear Friends,

Let's discuss one of the powerful Microsoft Excel functions: Lambda. The Lambda function is a revolutionary addition that allows you to define your own custom functions directly within Excel, transforming the way you approach complex calculations and data manipulations.

This video provides an introductory note to understanding and utilizing the Lambda function, demonstrating how it can streamline your workflows and enhance the efficiency of your spreadsheets. By creating reusable formulas, you can simplify intricate operations, making your work more organized and easier to manage.

Office-365 Part-5 Create new functions using lambda: https://youtu.be/73hl6Es5jUI

Happy Learning

Neeraj

[Neeraj Kaushik]

Dear Friends,

The LET function in Excel 365 is a fantastic tool that allows you to define named intermediate calculations within a formula. This significantly improves the readability, performance, and efficiency of your spreadsheets, especially when dealing with complex calculations that reuse the same expressions multiple times.

In this video, I provide an introductory overview of the LET function, demonstrating how it can simplify your formulas, make them easier to understand, and ultimately enhance your productivity. By assigning names to results of calculations, you can avoid repetition and make your formulas much more concise and manageable.

Office-365 Part-6 LET function: https://youtu.be/EvzDYZhtamU

Happy Learning

Neeraj

[Neeraj Kaushik]

Dear Friends

The FILTER, GROUPBY, and PIVOTBY functions represent a new family of dynamic array functions in Excel. They are designed to streamline data analysis by allowing us to perform complex data manipulations with a single formula, eliminating the need for traditional methods like PivotTables or manual sorting.

The FILTER function extracts a subset of data from a range based on specific criteria we define.

The GROUPBY function aggregates data by grouping rows, similar to a simplified PivotTable, but as a formula that automatically updates.

The PIVOTBY function is a more advanced version that allows us to group data by both rows and columns, providing a dynamic and flexible way to create summarized reports.

These functions automatically "spill" their results into adjacent cells and update in real-time as the source data changes. They give us a powerful, formula-based alternative for data summarization and reporting.

I've explained these functions in this video:

Office-365 Part-7 Lookup Family functions filter, groupby, pivotby: https://youtu.be/Wc3COnGs_7k

Happy Learning

Neeraj

[Oshin Gulsia]

Impressive information Dear Sir. 

Thanks 🙏🙏

Regards

Oshin Gulsia

Doctorate (Food Business Management) and Technical Superintendent at H.P MILKFED Shimla
National Institute of Food Technology Entrepreneurship and Management(NIFTEM)
(Deemed to be University under De novo Category & Autonomous institution under MoFPI, Govt. of India)
Plot No. 97, Sector-56, HSIIDC Industrial Estate, Kundli, Distt. Sonepat,
Haryana, India-131028. 
Mobile No. +91-9459339001,
Email: oshin.g...@gmail.com  

Links:

https://www.researchgate.net/profile/Oshin-Gulsia

https://www.scopus.com/results/authorNamesList.uri?

https://orcid.org/0000-0002-9925-0653

https://scholar.google.com/citations?user=CCBZjCkAAAAJ&hl=en

https://www.cyberwit.net/authors/oshin-gulsia

https://hillpost.in/author/oshingulsia/

--
The members of this group are expected to follow the following Protocols:
1. Please search previous posts in the group before posting the question.
2. Don't write the query in someone's post. Always use the option of New topic for the new question. You can do this by writing to dataanaly...@googlegroups.com
3. It’s better to give a proper subject to your post/query. It'll help others while searching.
4. Never write Open-ended queries. This group intends to help research scholars, NOT TO WORK FOR THEM.
5. Never write words like URGENT in your posts. People will help when they are free.
6. Never upload any information about National Seminars/Conferences. Send such information
in personal emails and feel free to share any RESEARCH-related information.
7. No Happy New Year, Happy Diwali, Happy Holi, Happy Birthday, Happy Anniversary, etc. allowed in this group.
8. Asking or sharing Research Papers is NOT ALLOWED.
9. You can share your questionnaire only once.
---
You received this message because you are subscribed to the Google Groups "DataAnalysis" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataanalysistrai...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/dataanalysistraining/CAAd%3Dc8Nj89htPMhoZOk77w4k%3DRuJ-%3D8TCG4RQOAN5VFNPJMCbQ%40mail.gmail.com.

[Dr. Jamaluddeen (Jamal)]

I found one more thing. If anyone does not have an institutional email, a personal email created with MS is working fine. My personal email, created with MS more than a decade ago (shown in the red-colored rectangle in the attached file), is working fine.

--

Thanks & Regards,

Dr. Jamaluddeen 

Assistant Professor

Department of Commerce

University of North Bengal

Siliguri, Darjeeling 

West Bengal - 734013

E-mail: jamal...@nbu.ac.in

Mobile: (+91)-74  99 61  51  56
___________________________________________________________________________________

"The mediocre teacher tells, the good teacher explains, the superior teacher demonstrates and the great teacher inspires."  ― Charles William Eliot

--

The members of this group are expected to follow the following Protocols:
1. Please search previous posts in the group before posting the question.
2. Don't write the query in someone's post. Always use the option of New topic for the new question. You can do this by writing to dataanaly...@googlegroups.com
3. It’s better to give a proper subject to your post/query. It'll help others while searching.
4. Never write Open-ended queries. This group intends to help research scholars, NOT TO WORK FOR THEM.
5. Never write words like URGENT in your posts. People will help when they are free.
6. Never upload any information about National Seminars/Conferences. Send such information
in personal emails and feel free to share any RESEARCH-related information.
7. No Happy New Year, Happy Diwali, Happy Holi, Happy Birthday, Happy Anniversary, etc. allowed in this group.
8. Asking or sharing Research Papers is NOT ALLOWED.
9. You can share your questionnaire only once.
---
You received this message because you are subscribed to the Google Groups "DataAnalysis" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataanalysistrai...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/dataanalysistraining/CAAd%3Dc8MH2RjPhv8Ym5p0B2hhW8k7afG4Q5cDZp32f%3DgJvgL7%2BQ%40mail.gmail.com.

[Neeraj Kaushik]

Dear Friends,

Let's continue our discussion on two incredibly useful Excel functions: IFS and SWITCH. 

These functions are essential for anyone looking to build more dynamic and efficient spreadsheets by handling multiple conditions with ease.

The IFS function allows you to check multiple conditions and return a value corresponding to the first true condition, providing a more streamlined alternative to nested IF statements. The SWITCH function, on the other hand, evaluates an expression against a list of values and returns the result corresponding to the first match, offering a clean and concise way to handle multiple outcomes based on a single input.

In this video, I have provided a comprehensive introductory note to understanding and utilizing both the IFS and SWITCH functions, demonstrating how they can simplify complex logical tests and enhance the readability and maintainability of your formulas. By mastering these functions, you'll be able to create more robust and adaptable spreadsheets, significantly boosting your productivity.

Office-365 Part-8 IFS and SWITCH functions: https://youtu.be/iyYcSezxGpE

Happy Learning

Neeraj