Ditch PivotTables! Self-Updating Excel Totals with Dynamic Image (Drag & Drop!)

Рет қаралды 34,267

2 ай бұрын

Tired of refreshing PivotTables? This video reveals a SECRET Excel trick for creating SELF-UPDATING TOTALS that STAY WITH YOU as you scroll! Plus, learn how to create a DYNAMIC IMAGE of your data that you can DRAG & DROP anywhere! Say goodbye to wasted time and hello to Excel EFFICIENCY! Perfect for both beginners and experts!
Download Practice File 👉 tinyurl.com/tybjevpd
💕Follow Us On💕
🌎 WhatsApp Channel 👉 whatsapp.com/channel/0029VaZe...
🌎 Facebook 👉 / xcelsive
🌎 KZfaq 👉 / @xcelsiveenglish
🌎 Rumble 👉 rumble.com/user/Excelsive
🙋‍♂️Credits
Image by benzoix on Freepik
🎯Keywords🎯
Excel Tips, PivotTable Alternatives, Self-Updating Totals, Dynamic Image, Drag & Drop Excel, Excel Efficiency, Large Datasets, Excel Shortcuts (Beginners & Experts)
🎯Tags🎯
excel,excel tips,floating button in excel,floating,microsoft excel,excel tutorial,floating image in excel,excel tips and tricks,floating cell in excel,floating picture in excel,floating bar chart in excel,ms excel,hr analytics in excel,pivot tables in excel,create live floating photo in excel sheet,scrollable table in ms excel,floating chart,pivot tables in excel examples,learn excel,floating bar chart,floating image excel,floating graph excel, excel, pivottable, totals, dynamic image, drag & drop, efficiency, large data, shortcuts, beginners, experts
Disclaimer 👉 The logos, names, addresses, phone numbers, and any other personal or proprietary information depicted or mentioned in this content are used for illustrative purposes only. Any resemblance to actual persons, living or dead, businesses, organizations, or events is purely coincidental. The inclusion of such information does not imply endorsement or affiliation with any entity. All trademarks, logos, and brands appearing in this content are the property of their respective owners. We do not claim ownership of any third-party content referenced herein. If you believe that any information included in this content infringes upon your rights or is inaccurate, please contact us immediately for prompt correction or removal.

Пікірлер: 66

@Nastyace2012 Ай бұрын

I have wanted to do this for the better part of a decade now! I asked everyone I knew and NOONE knew how to do this! THANKS! You simply have no idea how much this helped!

@XcelsiveEnglish Ай бұрын

Glad I could help!

@soniccheese01 Ай бұрын

Interesting idea. Would not using a split so you could scroll down in the bottom pane, whilst keeping the table/sumif output in the top pane in view be easier.

@XcelsiveEnglish Ай бұрын

That's a great idea! However, you will be sacrificing the screen real estate by doing so.

@eleghari Ай бұрын

OR make all your changes and then refresh the pivot table only once 😛

@XcelsiveEnglish Ай бұрын

That could work, only until we make a mistake and realise it only after finishing the file and finally refreshing the pivot table 😄

@renatorosco325 Ай бұрын

Use the unique function and/or some of the advanced filter function or hash operator magic to get you pivot replacement more flexible.

@XcelsiveEnglish Ай бұрын

Thanks for the suggestion

@mugundhans400 Ай бұрын

Amazing creativeness and appreciate it

@XcelsiveEnglish Ай бұрын

Thanks for the support

@unnikrishnansanthosh Ай бұрын

great idea, thank you for sharing

@XcelsiveEnglish Ай бұрын

Thanks for watching!

@kamaur01 Ай бұрын

Why not freeze the top few rows and use your table idea? I love the image idea though. It gives me other options to think about. Thank you for the video.

@XcelsiveEnglish Ай бұрын

That works too, if you don't mind sacrificing your screen real estate!

@pamphlex Ай бұрын

This is smart!

@XcelsiveEnglish Ай бұрын

Thanks

@kurtisgillette Ай бұрын

You can split and create a new window of the same worksheet. Both solves the issues of clicking and dragging the camera image

@XcelsiveEnglish Ай бұрын

That would sacrifice the screen real estate.

@BoraHorzaGobuchul Ай бұрын

Make the totals a separate sheet. Open a new window with that sheet. Stick it to the right side of the screen - your sheet is empty there anyways. Stick your data window to the left side of the screen.

@XcelsiveEnglish Ай бұрын

My sheet is empty, someone else's might not be. This is just a sample data, if you have something that spans dozens of columns, you are stuck with scrolling if you use a multi window setup.

@BoraHorzaGobuchul Ай бұрын

@@XcelsiveEnglish in that case, I usually transpose the totals to fit them in 1 or 2 rows, insert them above the column titles, and freeze panes if they're not already so that the title rows and totals are always visible

@XcelsiveEnglish Ай бұрын

that would work for you too.

@kevingodsave8893 Ай бұрын

Interesting solution. If you have 2 screens available, then View > New Window and drag the new window to the 2nd screen

@XcelsiveEnglish Ай бұрын

That is, if you have a multi monitor setup. 💰💰

@MSExcelExpert1 Ай бұрын

It seems you have done this work from the heart! Can you give me some tips after looking at my project too?🙏

@XcelsiveEnglish Ай бұрын

I have, thanks for noticing. Sure I can, share the file link here and I'll do what I can

@MonkEBoy-ud6kj Ай бұрын

Excellent!! The floating table image is a genius solution for several problems I’ve faced! For this specific example problem, I probably would have just added another column to the existing table with the sumif formulas in it… but that’s probably because I had no idea about the floating table images!

@XcelsiveEnglish Ай бұрын

Glad that I was able to help. Out of curiosity, what other problems you've faced that could have been solved by this?

@MonkEBoy-ud6kj Ай бұрын

@@XcelsiveEnglish I make a lot of Excel tools for other people at work. The latest is a worksheet for selecting only certain things from a much larger sheet that meet given criteria, and thus populating a form. What it doesn’t do is give a clean visual of everything you’ve chosen so far at a glance for reference, and this would work perfectly for that while not interfering with any of the surrounding formulas

@analysiscloud Ай бұрын

Great video! I tried copying and cutting the picture to other locations and everywhere it failed to work. That is, I copied it to another sheet within the same workbook, another sheet in a different workbook, into the body of an email, into a chat message in Teams and in all of those places the picture stopped functioning as a viewer/image to the data. If MSFT could implement this in those locations whilst keeping it dynamic then it would be a real game changer!! Data engineering would be changed over night!!

@edsta714 Ай бұрын

I haven’t tried the camera but copy paste special linked pictures does the same thing. You can cut it into other worksheets.

@analysiscloud Ай бұрын

@@edsta714 I can't get it to work on a different worksheet. I can't copy or cut it anywhere pasting as anything (picture or otherwise) without it failing. It works only within the same worksheet. So, it is good functionality but very limited in scope.

@XcelsiveEnglish Ай бұрын

I tried that too, if it had worked, it would have been a fitting climax to my video, but unfortunately I had the same results like yours. Your idea of using it emails, chat messages etc if implemented, could be revolutionary!

@XcelsiveEnglish Ай бұрын

Could you explain your method a bit?

@MrSamoDude Ай бұрын

It sounds like this is probably coming with future Microsoft Loop components.

@Hae3ro Ай бұрын

cool

@XcelsiveEnglish Ай бұрын

Thanks

@TamTinHoc2024 Ай бұрын

wow

@DPete27 Ай бұрын

You can use this to put a live excel table inside other MS programs also.

@XcelsiveEnglish Ай бұрын

You can, but it wont update when values are changed in Excel

@jz1536 15 күн бұрын

I need this but I also need to be able to filter

@Barcetta1 Ай бұрын

No need to dig for the camera-icon. Just copy your range and paste it as a Linked Picture.

@XcelsiveEnglish Ай бұрын

possibly. I haven't tried it myself

@Geevs80 Ай бұрын

Excel has a watch window feature, you could set it to watch your table

@XcelsiveEnglish Ай бұрын

It does. And it looks terrible!

@gavin.d.m Ай бұрын

Interesting idea but doesnt solve your pivot problem: from what i could see you copied the pivot table items then applied a SumIf formula. If you changed an Item description, added an item or removed it, the 'pivot' would collapse. Try using the Unique Formula then SumIf. That way if the Item description changed, the 'pivot' would change too. The normal pivot table would of course work, just in the same location and refresh.

@XcelsiveEnglish Ай бұрын

That's a pretty good suggestion. Thanks. It didn't occurred to me that there may be any change in my table. I did tried your suggestion and it works flawlessly.

@rtshort Ай бұрын

Might be nice to use Unique for the item name. if you add a new item, it would pick it up too. Not sure how the total would work out though.

@XcelsiveEnglish Ай бұрын

I have tested it, it works fine. I populated the item list with UNIQUE and then used SUMIF in the next column. I guess I should have used UNIQUE in my video and made the process future proof 😎

@BrianRussell76 Ай бұрын

As it rolls out, PIVOTBY is a great option here too

@rtshort Ай бұрын

Just saw a video on PIVOTBY just the other day. Waiting for a reason to use it. :)

@XcelsiveEnglish Ай бұрын

You're right, it could replace the pivot table, but the issue of scrollable and always accessible results would still have remained. By the way, I have a video about PIVOTBY & GROUBY here kzfaq.info/get/bejne/gbaFotmS19SliHk.html

@XcelsiveEnglish Ай бұрын

May be this could sway you, kzfaq.info/get/bejne/gbaFotmS19SliHk.html

@jonr6680 Ай бұрын

Image camera feature is new to me so this is fantastic to see. This doesn't solve the problem of the image window being static tho... A true solution is - Pivot on another sheet, add another workbook view, split the screen with the two windows, simplez! Then you can scroll down one sheet and the other stays static. The auto refresh of pivot tables is probably solvable with VBA. OR don't use a pivot but build your own set of formulae.

@XcelsiveEnglish Ай бұрын

Splitting the screen is the most common suggestion I have received for this video. I normally avoid that coz it means limiting my working area. About using VBA to update pivot table, I wanted something that is easy enough for beginners. Furthermore, even if was able to update the pivot table automatically the issue of it being stuck at one position still remains.

@thesexiestguy Ай бұрын

@@XcelsiveEnglishVBA can move the pivot table as well...based on Selection Change or Change Event...

@XcelsiveEnglish Ай бұрын

Agreed. But I needed something simple enough that beginners can use too. VBA is a tad bit advanced don't you think?

@thesexiestguy Ай бұрын

@@XcelsiveEnglish I have never used Camera tool because I never had the need. I'd resort to VBA for this kind of stuff because there is no other way around it. For ease of use for n00bs, VBA could be shared via a module or an add-ins perhaps. We all have to start somewhere and if we don't, we will always be n00bs. Having said that, I'm not undermining your work in anyway but more like trying to prove that it doesn't have to be this and this only. I said what I said because if VBA were already considered for refreshing Pivot table, moving it shouldn't be a limiting factor for not using VBA. If I were to figure out a solution, I'd use a modeless userform with something like a listview containing a portion of the required range and as bonus feature, I'd probably make the userform transparent and even click-through-able since you always make a point to emphasize screen real estate. Having said that, the code wouldn't be that hard (but not n00b-friendly for sure) but reusability would be very high, thus, so much fun. Now that, I thought of it, I'd probably make it after my current personal VBA projects are finished!

@XcelsiveEnglish Ай бұрын

I agree. But from my position, I am still trying to find my niche here. If in future, more tech savvy videos perform better, you will see those here.