Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

Author Topic: Excel Help and Problems  (Read 159780 times)

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7273
  • Total likes: 288
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #520 on: January 31, 2016, 08:26:25 AM »
If I have a bunch of data that I want to sort, and a bunch of data that I don't want to sort on the same tab. Often I just insert a bunch of blank rows in between them and are then able to sort/filter the ones on top and not the ones after the break.

Why does this not work sometimes?

I guess I can set the top ones as a table, but wondering when excel sees blank rows as a break and when as part of the data?


Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7273
  • Total likes: 288
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #521 on: February 03, 2016, 06:36:20 PM »
Cool tip, if you have a sheet with cells that are locked for formating, you can drag unlocked cells there and replace the locked ones.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12112
  • Total likes: 2165
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #522 on: February 03, 2016, 08:27:55 PM »
Sneaky! :)

Another cool tip: if you want to type something in a whole bunch of cells at once (without copy/paste) - select all the cells, type your text, and press control-enter.
Workflowy. You won't know what you're missing until you try it.

Offline MarkS

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2009
  • Posts: 3742
  • Total likes: 28
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #523 on: February 03, 2016, 08:50:48 PM »
My favorite tip is grouping tabs. When you have a spreadsheet with say 20 tabs, and all 20 are formatted similarly and you need to do identical things to all 20 (add a header and documentation on top, do formulas at the dn of the data in a specific column, highlight certain rows etc.

When tabs are grouped, work that you do on one tab is auto applied to all the tabs grouped!!! Can be a huge time saver. Just beware to only do it if all tabs are indeed formatted the same. Otherwise you may by mistake overwrite date in a different grouped tab.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2655
  • Total likes: 51
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #524 on: February 03, 2016, 10:47:42 PM »
My favorite tip is grouping tabs. When you have a spreadsheet with say 20 tabs, and all 20 are formatted similarly and you need to do identical things to all 20 (add a header and documentation on top, do formulas at the dn of the data in a specific column, highlight certain rows etc.

When tabs are grouped, work that you do on one tab is auto applied to all the tabs grouped!!! Can be a huge time saver. Just beware to only do it if all tabs are indeed formatted the same. Otherwise you may by mistake overwrite date in a different grouped tab.

You can also pull data. It's called 3D referencing.

For example, if you want to add all cells B5 from multiple sheets, you can write =sum(sheet1:sheet9!B5)
Very easy to create totals.

http://www.excel-easy.com/examples/3d-reference.html
office support

Offline Jkhein

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2010
  • Posts: 4538
  • Total likes: 13
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #525 on: February 07, 2016, 11:19:04 AM »
When I filter a column, and then use the sum function to sum up the total for that column, it is giving me the total including all the hidden rows, what is the proper way to do it, that the row numbers should show 1,2,3,4 instead of their original ones that causes the problems.

(I saw online that I can use subtotal,9 but is that the only way?)
Somehow I don't have this problem with all files
« Last Edit: February 07, 2016, 11:27:02 AM by Jkhein »

Offline MarkS

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2009
  • Posts: 3742
  • Total likes: 28
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #526 on: February 07, 2016, 11:32:08 AM »
When I filter a column, and then use the sum function to sum up the total for that column, it is giving me the total including all the hidden rows, what is the proper way to do it, that the row numbers should show 1,2,3,4 instead of their original ones that causes the problems.

(I saw online that I can use subtotal,9 but is that the only way?)
Somehow I don't have this problem with all files
FYI when you filter if you want to be able to copy/paste the filtered data to a different tab without copying the hidden data, use Go to special, and select visible cells only.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2655
  • Total likes: 51
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #527 on: February 07, 2016, 11:33:50 AM »


When I filter a column, and then use the sum function to sum up the total for that column, it is giving me the total including all the hidden rows, what is the proper way to do it, that the row numbers should show 1,2,3,4 instead of their original ones that causes the problems.

(I saw online that I can use subtotal,9 but is that the only way?)
Somehow I don't have this problem with all files

What do you mean by row numbers should show 1,2,3,4? And what problem were the hidden rows causing?

Offline Jkhein

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2010
  • Posts: 4538
  • Total likes: 13
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #528 on: February 07, 2016, 11:56:09 AM »
FYI when you filter if you want to be able to copy/paste the filtered data to a different tab without copying the hidden data, use Go to special, and select visible cells only.
Regular copy/pasting the data works for me, it does not copy/paste the hidden cells

What do you mean by row numbers should show 1,2,3,4? And what problem were the hidden rows causing?
After I filter, I get all the results, and the row numbers on the left side are like their originals, eg. 431,482,499.501. When I drag the range of those cells, the result will be A431:A501 which is a wrong calculation.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2655
  • Total likes: 51
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #529 on: February 07, 2016, 01:25:11 PM »
Subtotal is the way to go.

If you don't want the hidden cells at all you can Ctrl+G -> special -> visible cells only, then copy and paste somewhere else, and you have only the filtered rows.

Offline Jkhein

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2010
  • Posts: 4538
  • Total likes: 13
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #530 on: February 07, 2016, 10:35:20 PM »
is there a way to make that when pressing enter it should go to the beginning of the next row?

Offline Der Deutsche Jude

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Jan 2016
  • Posts: 893
  • Total likes: 38
  • DansDeals.com Hat Tips 3
    • View Profile
  • Location: Brooklyn, NY
  • Programs: AA, CA, FAA, GA, NA, OA, WA
Re: Excel Problem
« Reply #531 on: February 07, 2016, 10:59:59 PM »
is there a way to make that when pressing enter it should go to the beginning of the next row?
http://www.excelbanter.com/showthread.php?t=178107

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12112
  • Total likes: 2165
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #532 on: February 08, 2016, 10:46:33 AM »
is there a way to make that when pressing enter it should go to the beginning of the next row?
If you start at the beginning of a row, and press tab to get from cell to cell in that row, when you press Enter it will go to the beginning of the row
Workflowy. You won't know what you're missing until you try it.

Offline Zevi16

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Mar 2015
  • Posts: 2091
  • Total likes: 65
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #533 on: February 11, 2016, 03:43:38 PM »
I have 2 laptops, my old one has only got open office. I now have a new computer which only has microsoft office. The Open office file ?my new laptop and i cant find a way to open it on excel. How would i transfer it in a good format over to excel?

Offline DanH

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Oct 2011
  • Posts: 2180
  • Total likes: 26
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Problem
« Reply #534 on: February 11, 2016, 03:49:29 PM »
I have 2 laptops, my old one has only got open office. I now have a new computer which only has microsoft office. The Open office file ?my new laptop and i cant find a way to open it on excel. How would i transfer it in a good format over to excel?
Within OpenOffice you can save as a MicrosoftOffice file.
For tech help feel free to Telegram me @DanTechSupp

Offline Zevi16

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Mar 2015
  • Posts: 2091
  • Total likes: 65
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #535 on: February 11, 2016, 03:50:50 PM »
Within OpenOffice you can save as a MicrosoftOffice file.
Hmm I guess I'm going to have to revert back to my old laptop. Thanx

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5156
  • Total likes: 575
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #536 on: February 11, 2016, 05:12:14 PM »
Hmm I guess I'm going to have to revert back to my old laptop. Thanx
Or install OpenOffice on the new laptop and do it all on there.

Offline DanH

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Oct 2011
  • Posts: 2180
  • Total likes: 26
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Problem
« Reply #537 on: February 11, 2016, 05:35:46 PM »
For tech help feel free to Telegram me @DanTechSupp

Offline Zevi16

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Mar 2015
  • Posts: 2091
  • Total likes: 65
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #538 on: February 12, 2016, 01:21:08 AM »
Or convert it...
http://www.zamzar.com/convert/odt-to-doc/

or
http://odf-converter.sourceforge.net/
Doesn't work. Comes out the same way if I open the .ods file through excel. Illegible

Offline DanH

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Oct 2011
  • Posts: 2180
  • Total likes: 26
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Problem
« Reply #539 on: February 16, 2016, 06:37:38 PM »
I have 2 laptops, my old one has only got open office. I now have a new computer which only has microsoft office. The Open office file ?my new laptop and i cant find a way to open it on excel. How would i transfer it in a good format over to excel?
what version of office do you have now?

can you upload the .ods to google sheets and then download as excel?
For tech help feel free to Telegram me @DanTechSupp