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

Watch on YouTube

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