Excel can be fun and contagious. CD Drive Open and Close....if it is still there
Excel can be fun and contagious. So that some may remember the old days and some may have fun playing with it today Excel doesn't always have to be serious, it can also be funny 🙂 Have fun everyone, enjoy the time. Wish everyone a nice day or night with a lot of health, joy and love. Nikolino I always know that I don't know anythingTables are fantastic but cumulative totals are a pain
Excel Tables have too many benefits to list here, if you've never used them then check out my article here Tables = Ctrl +T One frustration I do have though is that cumulative totals in a column are not straightforward. In "normal" Excel to get a running total you just add the cell above to the cell on the current row, and that's it. For a table, with it's structured references and headings this proves problematic. Also, ideally you want a running total that works when you insert rows into the Table or add lines of data to the bottom of it. *** Update 6/12/2019*** My preferred approach is now shown in a post further down the responses https://techcommunity.microsoft.com/gxcuf89792/board/message?board.id=ExcelGeneral&message.id=45961#M45961 and I've attached a demo file here ******************************************************************* This was my original post... So here are 2 options, and both make use of the INDEX function. INDEX has several uses and one of them is to make a dynamic range. This is a very odd concept when you first see it written e.g. =SUM(A1:A10) can be re-written with an INDEX formula as = SUM( INDEX(A:A , 1) : INDEX(A:A , 10) ) Essentially it's saying SUM everything between the 1st cell of A:A and the 10th cell of A:A Stick with me, like I say, I know it's unfamiliar and a little weird. Let's apply this to a Table that has weekly units sold for January and February To keep things simple I've set it so that we've sold 1 unit a week. So the formula we put in the Cumulative Sum column is =SUM( INDEX( [Units Sold] , 1) : [@[Units Sold]]) So it adds up the values from row 1 of the Units Sold column as far as each row (that's where the @ [Units Sold] comes in) I'll agree it's not a very attractive formula but it works and I wish there was a simpler way. Importantly it works if you insert rows or add more data to the bottom of the Table. Brilliant! Option 2 : A little more advanced If you've stuck with me this far then it's worth reading this extra bit... The SUM approach has a drawback. If you filter the table for February you still get the YTD cumulative rather than just the February Cumulative An alternative is the little known AGGREGATE function (you could also use SUBTOTAL). =AGGREGATE( 9, 7, INDEX( [Units Sold] , 1) : [@[Units Sold]] ) ) The 9 means SUM The 7 means ignore Hidden Rows AND Errors So when the data is filtered the rows are hidden, and the Aggregate works nicely. Even if one of your values was an Error e.g. #VALUE or DIV/0 the cumulative would ignore it and the total still works. This ignoring of elements can come in really useful, but just be careful that you really do want to do this! Spotting errors are normally a good thing. Any suggestions of alternative approaches would be appreciated. Hope you find this useful Wyn My other Linked In articles AMAZING EXCEL SOLUTIONS www.accessanalytic.com.auExcel Community: Simplifying Spaces and Labels
Hi all, As you may have noticed already, we as the Excel Team have done a bit of "spring/summer cleaning" for the community. We have received feedback that the number of "spaces" was simply too many at nine, so we have pared things down. Now, there are three community spaces: Excel: this will be the primary place for posting content, as many of the old spaces have been migrated as labels will find a good home here BI and Data Analysis: this will be a good place for posting about tasks and questions that cut across Excel, Power BI, and other topics in this realm Resources and Community: this will increasingly become a place for folks to share sample files and templates with each other Q: What happened to the other spaces that used to exist? A: They have since been rolled up as "Labels", which you can find in the "More Resources" sidebar under "Labels" of any of the three spaces mentioned above. Look for these on the right side: Please let us know if you have any other questions. Thanks for reading!
New free Excel training courses from LinkedIn Learning on Support.Office.com
We have a whole new collection of training courses to help you get the most out of Excel 2016. Learn how to enter and organize data, create formulas and functions, build charts and PivotTables, and use other time-saving features. Please let us know what you think of these new courses. They’re free, along with the rest of the courses and videos in the Office Training Center. Best, Freya Office NewsroomPrivate messages vs Community Discussion
Hi, From time to time I receive private messages with this or that question. I'm sorry if I don't answer on all of them, but my priorities are 1) Do my main job 2) Comments/answers on community discussions if I can 3) Private messages Time is limited. I highly recommend to post your questions on community discussions. Here are a lot of great guys with deep expertise and knowledge, much more chances you have an answer if ask the community, not someone personally. Another point, people who are visiting TechComm may find an answer on their questions as well browsing our discussions. No chances if questions are hiding within private correspondence. Thank you for understanding.Sample Power Pivot and Power BI Desktop Models
Hi, I've recently built and published two sample data models - one with Power Pivot for Excel and another with Power BI Desktop to support training and testing of model and report authoring features in both tools (e.g. DAX metrics, chart/visual types). Both models use the https://msdn.microsoft.com/library/mt734199(v=sql.1).aspx sample data warehouse database for SQL Server 2016 as the source and they share the same schema and metadata. Each can be downloaded from the following two links: http://tinyurl.com/z2gyhps https://insightsquest.com/2016/10/08/sample-power-bi-desktop-model-available-for-download/ The models contain multiple star schemas so they may be a bit larger than common XLSX and PBIX models but this also makes them more powerful and useful for learning and understanding evaluation context. I hope these can be of value but please feel welcome to comment here or on my blog if you have problems or issues. Isn't it great that we TWO great BI tools like this? -Bretthow to run personal macro workbook creating folder
hi every one, Can give anyone best suggestion to me to run personal macro workbook but the first-time I'm trying to run that, when it try to run personal macro workbook one message is showing "personal macro workbook in the startup folder must stay open for recording, unable to record" can any give best steps to run macro and how to creat a folder i dont know regards vikasdevHow to Be an Excel Detective: Finding and Highlighting Formulas
Hi everyone, I recently wrote a blog post on some simple, yet powerful, techniques for anyone who works with Excel spreadsheets, especially those with complex data. I wanted to share a summary of it with this community, as it might be helpful to others who are looking to understand and protect their work. The post covers two main things: Quickly Revealing All Formulas: A simple keyboard shortcut (Ctrl + `) or the "Show Formulas" option can instantly reveal all formulas in a worksheet. This is a great way to quickly see how a spreadsheet is structured. Permanently Highlighting Formulas: The article shows how to use the "Find & Select" > "Formulas" feature to select all cells containing formulas and then permanently highlight them with a fill color. This visual cue can help prevent accidental edits and protect your data. Watch This in Action: For a step-by-step guide on how to use these techniques, you can watch the video on my https://www.youtube.com/@BIGurus. 🔗 https://youtu.be/1x-1dbqlWXk You can also read the full article here: https://medium.com/@anandsharad/how-to-be-an-excel-detective-finding-and-highlighting-formulas-fe9d4fdbc1b1 I'd be happy to answer any questions you have or discuss other Excel tips and tricks in the comments!
How to limit "Forwarding" of a shared Excel Workbook
Hello, I am trying to understand if this is possible so please bear with me. I have shared access to my Workbook with an individual from another agency, which is working well. They in turn shared it to a coworker without my knowledge. Thankfully there is no inherit confidentiality concerns but it presented a problem. I never received an email about the "forwarding" and was only made aware when Individual A informed me they had shared it with their coworker. They did receive an email that it was "shared" but I did not. Can I limit who shares access to the workbook? The only permissions I am able to see that are changeable are "Edit" and "View" When I am using the word "forwarding", I am thinking of how you can forward a scheduled appointment in Outlook, which sends an email to the host, letting them know the meeting was forwarded. Thank you so much for your help.
