ChatGPT For Excel: Tips & Tricks to 10X Your Productivity

👩‍🏫 LEARN MORE in my Excel courses: bit.ly/excelandchatgpt
➡️ FREE 239 Excel Shortcuts for Windows & Mac: bit.ly/excelgptshortcuts
Today, we're going to explore ways you can leverage ChatGPT to transform the way you work with Excel. Imagine having formulas effortlessly crafted for you and uncovering shortcuts to make everything simpler.
LEARN MORE
===========
📰 EXCEL NEWSLETTER - join 450K+ subscribers here: www.myonlinetraininghub.com/e...
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
📢 Please leave me a COMMENT. I read them all!
🎯 FOLLOW me on LinkedIn: / myndatreacy
🎁 SHARE this video and spread the Excel love.
Or if you’re short of time, please click the 👍
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
#ChatGPT #Excel #ExcelTips

[@MyOnlineTrainingHub]

❓What's the one Excel task or formula that always seems to stump you? LEARN MORE in my Excel courses: bit.ly/excelandchatgpt

[@kerynataly]

Hello, I have a question about power query. I created date table, added events. But events with same date create duplicated row. I have same date in two rows. I want to have one date value and the names of events in two separated columns.

[@roshannair4268]

Index match always stumps me. It seems simple, but I always find myself watching videos or reading about it

[@chrism9037]

Great video Mynda! I've started to use ChatGPT several times when I get stuck with a formula, and also in DAX.

[@johnkasongo4762]

Index & match always stump me!!!!😔!!! Fantastic tutorial Mynda!

[@MyOnlineTrainingHub]

Thanks 🙏 here’s a tutorial on INDEX & MATCH: www.myonlinetraininghub.com/excel-index-and-match-functions

[@piterqantropus]

Amazing Mynda, thanks for all your content!

[@MyOnlineTrainingHub]

Thank you for the support!

[@al3xj]

Using the same for power query Mynda, in these busy days it's helped make it more accessible to update values easily in the query editor and 'how to tricks'

[@MyOnlineTrainingHub]

Awesome to hear 😁

[@elecirlisboa9155]

Excelente vídeo Mynda! Parabéns, vou incluir no ChatGPT no meu dia a dia! ☺

[@MyOnlineTrainingHub]

Wonderful to hear 😊🙏

[@iankr]

Many thanks, Mynda.

[@MyOnlineTrainingHub]

Thanks for watching 😊🙏

[@cjkleynhans]

Super!!!

[@MyOnlineTrainingHub]

Thank you! Cheers!

[@windyrainStorm]

Can you please make videos about REDUCE function and the use of MAP and FILTER

[@MyOnlineTrainingHub]

On the list 😉 FILTER is already done here: kzbin.info/www/bejne/kHS0cqqbpKaXhdk

[@Windex237]

My top formula issues tend to be dynamic hyperlinks (don't use them enough and keep forgetting), long IF...SUBSTITUTE chains, working with dates in array formulas and LAMBDAs.

[@MyOnlineTrainingHub]

Thanks for sharing!

[@Acheiropoietos]

Did you speed up the ChatGPT responses Mynda? Mine are always so slow. Great post, as ever. 👍

[@MyOnlineTrainingHub]

Yes, I did but not by much.

[@shakiraasfoor7599]

🙏 thank

[@MyOnlineTrainingHub]

Pleasure 😊

[@NeshaStanic]

ChatGPT 3.5 o4 4.0 or Copilot or Bard? BTW, my stumps are: - comparing different tables with similar data to find differences - not able to import data from tables from various web sources, just won't import (mainly ones with daily currencies etc - creating centralized data table out of very complex business, so that various reports are created, and sharing it with various parties, connecting with SharePoint data, Outlook... All in on 365 platform, company wise.

[@MyOnlineTrainingHub]

Lots to tackle there. You can use Power Query to compare tables: www.myonlinetraininghub.com/excel-compare-two-lists

[@paulbriggs7126]

my tough one, have not solved it yet, is to have a sortfilter command that is looking at a table and it outputs a list. Then I want to put another sortfilter under that output to create another output area. If I leave one or 2 blank rows it works, then if I add more data to the original table to increase the length of the output, I get spill as there is not enough room for the top one to output before running into the second output. Was thinking it would be cool to have the distance between the 2 outputs expand based on the data size.

[@MyOnlineTrainingHub]

You could probably get around this by stacking the two ranges in one formula using VSTACK, rather than two separate formulas.

[@mathijs9365]

Not updating VBA is a mistake of MS. Excel online wont match excel desktop. Why use office script?

[@MyOnlineTrainingHub]

Microsoft are working towards bringing Excel online to parity with the desktop, but it will be a while.

[@mathijs9365]

I know. But takes too long. Tbh I dont think it will happen VBA is last updated in 2007. Thats 15 years. Yet office script is far behind VBA. And ppl still use VBA. I believe office desktop application will still be user favorite.

[@IamTheReaper911]

Hmm, I'm still on the fence with ChatGP, but it's definitely something to think about 😎

[@MyOnlineTrainingHub]

Yeah, it can be hit and miss still, but it’s improving.

[@jupiterjames4201]

Making a "Sumproduct IFs" function is always challenging

[@MyOnlineTrainingHub]

Indeed it is 👍

[@mikaelrundberg]

I use the excel sheets to make test or certificate documents and use "Rows to repeat at top". But the only way to get page numbers on each page is in headers/footer, i can't find a way to get it in to a cell in the part of "Rows to repeat at top" area that i picked.

[@MyOnlineTrainingHub]

Yes, page numbers are only added in the header or footer. You could manually add them in cells in the worksheet.

[@user-jt3nj2ko6z]

Trying to determine employee headcount by month using start dates and termination dates is a hassle. For me, it involves nexted IF statements that include AND and OR combinations to account for all the possible scenarios (e.g., an employee starts and leaves in the same month, starts partway through the month, etc.).

[@MyOnlineTrainingHub]

I’m thinking you might be able to write a custom lambda function for that, similar to how I wrote one to calculate tax: www.myonlinetraininghub.com/excel-lambda-function

[@user-jt3nj2ko6z]

Thank you for the idea. I’ve done that for other tasks, so I’ll give that a try. I love your videos!!!!😊

[@youranonymousfriend]

If I have a data in cell A1 to A10 and I want to paste it into filtered table. Suppose there is a filter applied and it hides say row 2 then 5 then 13 randomly so i want to skip them hidden rows while pasting. So my A1 goes to row 1 A2 goes to row 3 and A4 goes to row 6.

[@MyOnlineTrainingHub]

Pasting into filtered tables only works when copying a single cell or row.

[@53Talon]

I have a desktop computer that's not connected to the net for security reasons. If I want to bring my excel version up to date since it has windows 7 on it now. Is there a way to get the new version as a one time down lode file I can transfer to my computer?

[@MyOnlineTrainingHub]

The latest version of Excel is not designed to use offline. You could buy Excel 2021, but with 2024 coming out soon, you might prefer to wait. That said, I’m not sure it will run on Windows 7.

[@87CVH]

With let and lambda you can do apportionment with a single formula

[@MyOnlineTrainingHub]

Yes, those functions are game changers for 365 users. 👍

[@87CVH]

@@MyOnlineTrainingHub can u do a demo on the different apportionment methods as single cell formulas please?

[@the_right_path_14]

This world has gone crazy with productivity

[@MyOnlineTrainingHub]

I don’t see it stopping anytime soon 😁

[@Everyonelovesyou]

I face challenge when there are too much columns are there and I have to scroll to and fro a lot. 2ndly when I have to make Sales Budget for the year month wise dynamically on multiple criteria. 3rdly, I want my formula to spill both in rows and columns spontaneously. 4rth I cant see Pivotby and Groupby function I have tried every thing. I am using Office 365. and my excel is really slow it start calculating each time I copy formula down and across or try to save a file with ctrl+S

[@MyOnlineTrainingHub]

Thanks for sharing! In 365 you can CTRL+SHIFT+ mouse scroll to scroll left and right. Or you could use groups to temporarily hide/unhide columns: kzbin.info/www/bejne/b6HXdZSkjKqlnpY Spilling formulas into rows and columns is not available in all cases. Some formulas are constrained by the array or arrays limitation. PIVOTBY & GROUPBY are currently only available to 365 users on the beta channel. Slow Excel will be file specific. Sounds like your formula may be inefficient. 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

[@miguelangelponce8553]

Hi yMnda I can't download the workfile, would you please resend it?

[@MyOnlineTrainingHub]

There’s no file for this video, but you can download the cheat sheet.

[@emiliocruz91]

If and or formulas

[@MyOnlineTrainingHub]

Check out this video: www.myonlinetraininghub.com/excel-if-and-or-functions-explained

[@keylanoslokj1806]

I have a stocks portfolio that updates every time you open the document. Is there a way to make it stop asking to save the auto-changes every time you close it, cause it gets annoying?

[@MyOnlineTrainingHub]

Excel will always ask you to save changes if it detects changes in the file. I'm not aware of any way to prevent this.

[@keylanoslokj1806]

@@MyOnlineTrainingHub thanks Mynda!

[@paulbriggs7126]

save it on your onedrive and enable auto save in excel for the file.

[@ceomdistributionpesco7333]

Hi, hope you are fine, the request is can I share some Excel files having data for creating therein some conditional formulas & send them back to me?

[@MyOnlineTrainingHub]

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

[@ClaudioandMary]

My Challenge: Array formulas always are beyond me.

[@MyOnlineTrainingHub]

Thanks for sharing. I’ve found with the new dynamic array functions it’s easier to see how they work: www.myonlinetraininghub.com/excel-dynamic-arrays

[@HachiAdachi]

What stumps me: I just can't memorize how to write an absolute reference for a table column, i.e. TableName[@[Column Name]:[Column Name]].. Is @ supposed to sit inside or outside of [ ] ?? Is [ ] supposed to wrap around each column name or around both?? I do eventually figure it out, but not before several error messages... EVERY. SINGLE. TIME...😳

[@MyOnlineTrainingHub]

😁 I can never remember either!

[@HachiAdachi]

I feel a little comforted 😅

[@nessim.liamani]

"hot" topic :)

[@MyOnlineTrainingHub]

Thanks for watching 🙏😊

[@RandomSynr]

I am easily stumped by making a cell show nothing when there are no figures or text

[@MyOnlineTrainingHub]

I'm not sure what you mean. Surely if there are no figures or text in a cell then it isn't showing anything. You're welcome to post your question and sample Excel file that demonstrates what you mean on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@RandomSynr]

@@MyOnlineTrainingHub it is a cell that is part of a formula

[@MyOnlineTrainingHub]

Oh, ok. You can use two double quotes to return a blank cell. e.g. this will return a blank if the formula A1/B1 returns an error =IFERROR(A1/B1, "")

[@asdfqwer1675]

index and match 😭😭i never get it

[@MyOnlineTrainingHub]

Check out this video on INDEX & MATCH: 4 Mins. to MASTER Excel INDEX & MATCH formulas! kzbin.info/www/bejne/Y2XcdIumq5KXqMk

[@mrkenwu1]

Not sure about this... For me, your videos are easier to follow than ChatGPT.

[@MyOnlineTrainingHub]

Glad you like them!

[@rahulbakshi285]

Lamda function

[@MyOnlineTrainingHub]

Sure, get ChatGPT to help you write lambdas.

[@ExcelStrategy]

To use chatgpt they ask you your phone number... no way that thing gets my phone number !

[@MyOnlineTrainingHub]

I don’t recall it requiring my phone number 🤔

[@ExcelStrategy]

@@MyOnlineTrainingHub Yes it does