Excel - Sum Data Alt-Entered - Episode 2160
3,468 views · Published 23 October 2017 · 8:59 · Indexed 25 October 2026
Channel: MrExcel.com · 2017 · Education
Microsoft Excel Tutorial: Steve has to sum numbers that have been entered in a text column Welcome to another episode of the MrExcel netcast, where we help you master Excel and become a spreadsheet pro. In today's episode, we will be tackling a common issue faced by many Excel users - how to sum data that has been entered using the Alt+Enter shortcut. This is a common problem faced by those who work with budget data, where the numbers are entered in a text column with multiple lines separated by Alt+Enter. But don't worry, we have a solution for you! I recently received a question from a viewer who was struggling with this issue. They had inherited a budget spreadsheet where the numbers were entered in a non-traditional format, with multiple lines and random notes in between. This made it difficult for them to sum up the numbers for each cost center. But thanks to the power of Excel and our trusty tool, power query, we were able to find a solution to this problem. First, we will use power query to transform the data into a more manageable format. We will remove all the blank rows and split the data into rows based on the Alt+Enter delimiter. Then, we will further split the data to extract the dollar amount from each cell. Finally, we will use the SUM function to get the total for each cost center. And the best part? This solution is applicable for all versions of Excel, from 2010 to 2016 Office 365. But wait, there's more! We have a bonus tip for you. We will show you how to create a macro that will automatically update the worksheet whenever a change is made to the data. This will save you time and effort, as you won't have to manually refresh the data every time a change is made. And to make things even better, we have a special recommendation for you - the book "M is for (DATA) MONKEY" by our friends Ken Puls and Miguel Escobar. This book is a must-have for anyone looking to master power query and take their Excel skills to the next level. So, if you're tired of manually summing data that has been entered using Alt+Enter, then this episode is for you. We hope you find this solution helpful and don't forget to check out "M is for (DATA) MONKEY" for more amazing tips and tricks. Thank you for tuning in to the MrExcel netcast, and we'll see you in the next episode for more Excel mastery. Don't forget to like, share, and subscribe for more helpful Excel content. Until next time, happy spreadsheeting! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ #excel #microsoft #microsoftexcel #exceltutorial #excelhacks #excelchallenge #evergreen #personalfinance #powerquery This video answers these common search terms: how to separate alt enter in excel formula to sum numbers alt enter in excel Table of Contents: (00:00) Sum Numbers Alt-Entered in Excel (00:36) Goal: Summing data for each cost center (01:09) Using power query to solve the problem (02:00) Splitting data using a delimiter (03:07) Grouping and summing data (04:00) Using VLOOKUP to get the total (05:33) Refreshing the data (06:23) Addendum: Making the process even better (07:52) Clicking Like really helps the algorithm There are multiple lines in each cell, separated by Alt Enter Need to split those lines to rows, then parse the dollar amount from the middle of each cell Summarize by Cost Center Build a lookup table Get totals from the lookup table, using IFNA to ignore the errors in the blank row Bonus: Add an Event macro to update the worksheet when they change a cell. Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153985/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920