Spreadsheets and Tips -Excel and Google Sheets

[Dan Epstein]

Howdy, I have a confession, I hate numbers and the numbers hate me (Ask my bank manager).
I am, however, working with Excel and Google Sheets on a daily basis, pivoting, Vlookuping and mostly facing many hacks to make lives easier while working with existing or creating spread sheets.
I'll start with the 1st question, what do you prefer to work with, Excel or Google Sheets and why?
Let's help each other with the boring tasks this time

 

[Deleted member 57737]

I used Excel for a long time. But now its just more convenient to use google sheets. I use it for a programming project I have. Nothing fancy, just very simple calculations, if any. So I'm a noob. But I will have to expand that in the future. Main use is to organize the indexing of the memory I'm accessing within the software I'm building. Sounds complicated but its very simple. One column is the data index [0...x] the next one is a description of the content. So I just can add new indices and the sheet is spitting out the important numbers I have to use as index offsets when I'm reading certain parts of the memory.

There is a function I'd love to find and I don't know if that is a thing, it might be pretty niche: In the case the text is not fitting inside one cell, is it possible to have the whole text displayed only if you hover the mouse over said cell? I think I saw something like this in ms word with some kind of correction function. It was possible to mark a piece of the text and add a remark which only pops up when you hover the mouse over it.

 

[CallMeMaria]

Im in google sheets team - Just because i find it easy to access without looking for that specific excel file I had saved on the laptop. I had that excel imported to google sheets and now I use that only. Just simple calculcations, daily earnings, hourly average etc.

 

[KingMarti]

If its a sheet that I am working with then google sheets just because its convenient (and free is also a bonus) being able to access it through my account opposed to a specific device.
For csv files and sheets I get from other people libreoffice math mainly because I dont want to have to upload them to google drive to be able to read them, I dont normally need to access them more than once so it wouldn't be any benefit to having them on drive for me.

 

[Dan Epstein]

Okay, so the pros for Google Sheets on my end are:
1. It's free if you have a Google Account
2. It's easy to collaborate
3. It integrates with other Google products like Data Studio, Google Analytics
So here are the cons (and why I usually use Excel):
1. With Excel I don't need Internet access
2. An Excel file doesn't break as easily, especially when you convert another Comma Separated Value spreadsheet into it.
3. Power query allows me to work with separate files, append them, pivot and unpivot them.

The downside of Excel beside it being a paid for software, is that it doesn't plat too well with Mac computers.


As for programming @Gruner , dev teams will usually work with a live database, and the pulling of data or analysis will be made with SQL, or SQL based platforms like Power BI and Tableau.
So, please be honest about my next question, do you use Pivot tables or do you Cntrl+F and start manually calculating?

 

[KingMarti]

Okay, so the pros for Google Sheets on my end are:
1. It's free if you have a Google Account
2. It's easy to collaborate
3. It integrates with other Google products like Data Studio, Google Analytics
So here are the cons (and why I usually use Excel):
1. With Excel I don't need Internet access
2. An Excel file doesn't break as easily, especially when you convert another Comma Separated Value spreadsheet into it.
3. Power query allows me to work with separate files, append them, pivot and unpivot them.

The downside of Excel beside it being a paid for software, is that it doesn't plat too well with Mac computers.


As for programming @Gruner , dev teams will usually work with a live database, and the pulling of data or analysis will be made with SQL, or SQL based platforms like Power BI and Tableau.
So, please be honest about my next question, do you use Pivot tables or do you Cntrl+F and start manually calculating?

Maybe office 365 would be your best option then? since it's cloud based the mac thing shouldn't be an issue anymore, but I am not 100% sure if it has all the features of a local install, I imagine it would but couldnt say for sure.

 

[Dan Epstein]

Point well made, Mac simply doesn't support the heavier actions of Excel, that sometimes you have to do as a campaign manager with heavier sets of data that is just exported. Cleaning data by the way is one of the best actions you can do when you get a spreadsheet full of irrelevant shit, date written inconsistently, repeating values, numbers that are actually strings, etc.
So, why do we keep spreadsheets if we are not gonna play some pivots on them?

 

[KingMarti]

Point well made, Mac simply doesn't support the heavier actions of Excel, that sometimes you have to do as a campaign manager with heavier sets of data that is just exported. Cleaning data by the way is one of the best actions you can do when you get a spreadsheet full of irrelevant shit, date written inconsistently, repeating values, numbers that are actually strings, etc.
So, why do we keep spreadsheets if we are not gonna play some pivots on them?

I never have to mess that much with spreadsheets, I only use them for simple shit and checking through csv files of affiliate stats. Anything heavier than a time sheet I normally use a database for and use python to clean the data before its inserted, that tends to be more when I am scraping data than pulling csv files though. I know its something that can be done in spreadsheets but personally never had the need to learn... I'm kind of like every tool's a hammer if you hit it hard enough

 

[Deleted member 57737]

As for programming @Gruner , dev teams will usually work with a live database, and the pulling of data or analysis will be made with SQL, or SQL based platforms like Power BI and Tableau.
So, please be honest about my next question, do you use Pivot tables or do you Cntrl+F and start manually calculating?

Nothing fancy like that. The sheet isn't embedded into the code that I'm programming in any way. I'm using Reaktor, a DSP (digital signal processing) environment. There you can only import txt files consisting numbers that you have to export from the sheet. So I have one column of the numbers which are calculated in different ways. And one column with text for my comprehention. These texts are sometimes very long. So stretching these columns on the x axis isn't enough and would give me hundreds of one liners. The layout isn't readable at all. Thats why I'd like to know if there is this kind of hover function.

Thanks a lot for your effort.

 

[Deleted member 57737]

I'm kind of like every tool's a hammer if you hit it hard enough

lol love this!

 

[KylieJacobs]

A huge benefit to Google sheets is that you can share one, and each make changes to it in real time.

On the other hand, I feel like Excel can still do more, that or, I just find things easier because I am used to it.

I also like that Excel is private. I keep my sales info on my computer with Microsoft 365 since I don't really want that in the cloud.

I use Google sheets anytime that I want to be able to share something on a regular basis.

 

[Dan Epstein]

Nothing fancy like that. The sheet isn't embedded into the code that I'm programming in any way. I'm using Reaktor, a DSP (digital signal processing) environment. There you can only import txt files consisting numbers that you have to export from the sheet. So I have one column of the numbers which are calculated in different ways. And one column with text for my comprehention. These texts are sometimes very long. So stretching these columns on the x axis isn't enough and would give me hundreds of one liners. The layout isn't readable at all. Thats why I'd like to know if there is this kind of hover function.

Thanks a lot for your effort.

Sounds like you may need a scattered plot chart with a tool tip for hovering. It takes a little time to build. You can also export the file to excel and use it with Power BI.