T Nation

Anybody Good With Spreadsheets


#1

Boss wants me to make up a spreadsheet showing stock levels, monthly sales, where the sales came from (web, store, etc) and all that sort of shit.

If anyone can link to a template to make it easier for me I'd really appreciate it.

It's probably the sort of thing that's really easy if you're Xcel savvy, but I've not used it in years.

Thanks guys.


#2

I’m pretty sure they have a bunch on the microsoft office online site. I was looking for powerpoint templates and ideas. I think it had Excel shit for like business and finacial management type stuff.


#3

[quote]Yogi wrote:
Boss wants me to make up a spreadsheet showing stock levels, monthly sales, where the sales came from (web, store, etc) and all that sort of shit.

It’s probably the sort of thing that’s really easy if you’re Xcel savvy, but I’ve not used it in years.
[/quote]

This is a job for a pivot table. It will take you 30 minutes to learn to do, no need for a template/download.


#4

[quote]tsantos wrote:

[quote]Yogi wrote:
Boss wants me to make up a spreadsheet showing stock levels, monthly sales, where the sales came from (web, store, etc) and all that sort of shit.

It’s probably the sort of thing that’s really easy if you’re Xcel savvy, but I’ve not used it in years.
[/quote]

This is a job for a pivot table. It will take you 30 minutes to learn to do, no need for a template/download.[/quote]

This … or do you know anyone who can run a Sql query? Do you have any databases at work (access, SQL servers?) that collect/store transaction data?

I guess I’m asking how do you store your data? Because all that information is in there, already formatted to convert to a pivot table and organize exactly how you’re describing. Once you figure out how to manipulate/organize the data, it’s pretty damn easy and quick.

If you don’t or don’t know, then you’re in for a lot of data inputting which is fucking tedious … best hire an intern for that shit.


#5

If you are trying to import a large amount of data, just have whatever program you are running write it as a .csv file, then use the excel (*cough, piece of shit *cough) import wizard to load the .csv file.


#6

[quote]Aero51 wrote:
If you are trying to import a large amount of data, just have whatever program you are running write it as a .csv file, then use the excel (*cough, piece of shit *cough) import wizard to load the .csv file. [/quote]

^Great advice! As far as learning anything new on excel, youtube is my best friend.


#7

Pivot table as has been said. They’re not that difficult to use.


#8

Something like this (pivot is the table on the right side)?

*Numbers make no sense, lol…


#9

Fuck pivot tables. I’ve seen more ignoramuses piss away more hours trying to get that shit to work… When it could have just been done, reviewed, fixed and issued in half the time.

Maybe it’s because all the people I’ve seen try and sell me on them were dipshits, but fuck pivot tables.

Also, x2 on the shitty import wizard of excel.


#10

[quote]countingbeans wrote:
Fuck pivot tables. I’ve seen more ignoramuses piss away more hours trying to get that shit to work… When it could have just been done, reviewed, fixed and issued in half the time.

Maybe it’s because all the people I’ve seen try and sell me on them were dipshits, but fuck pivot tables.

Also, x2 on the shitty import wizard of excel. [/quote]

Pivot tables are great when you have a lot of data to organize and make basic sense out of (sums, counts, etc.) as long as the data is set up in database format.

If not, I agree it can be a big pain in the ass and it’s just as easy to sort it “manually” and plug in formulas (as long as you know what formulas to use/how to use them).

Tool for a job is all it is. Gotta know what you’re building before you pick the tools for the toolbox.


#11

[quote]polo77j wrote:

[quote]countingbeans wrote:
Fuck pivot tables. I’ve seen more ignoramuses piss away more hours trying to get that shit to work… When it could have just been done, reviewed, fixed and issued in half the time.

Maybe it’s because all the people I’ve seen try and sell me on them were dipshits, but fuck pivot tables.

Also, x2 on the shitty import wizard of excel. [/quote]

Pivot tables are great when you have a lot of data to organize and make basic sense out of (sums, counts, etc.) as long as the data is set up in database format.

If not, I agree it can be a big pain in the ass and it’s just as easy to sort it “manually” and plug in formulas (as long as you know what formulas to use/how to use them).

Tool for a job is all it is. Gotta know what you’re building before you pick the tools for the toolbox.[/quote]

Pivot tables are easy as hell to use. I don’t really see the issue.


#12

[quote]usmccds423 wrote:

[quote]polo77j wrote:

[quote]countingbeans wrote:
Fuck pivot tables. I’ve seen more ignoramuses piss away more hours trying to get that shit to work… When it could have just been done, reviewed, fixed and issued in half the time.

Maybe it’s because all the people I’ve seen try and sell me on them were dipshits, but fuck pivot tables.

Also, x2 on the shitty import wizard of excel. [/quote]

Pivot tables are great when you have a lot of data to organize and make basic sense out of (sums, counts, etc.) as long as the data is set up in database format.

If not, I agree it can be a big pain in the ass and it’s just as easy to sort it “manually” and plug in formulas (as long as you know what formulas to use/how to use them).

Tool for a job is all it is. Gotta know what you’re building before you pick the tools for the toolbox.[/quote]

Pivot tables are easy as hell to use. I don’t really see the issue. [/quote]

I will send you guys the names of the two people who’ve ruined them for me, and you can sort them out.


#13

[quote]usmccds423 wrote:

[quote]polo77j wrote:

[quote]countingbeans wrote:
Fuck pivot tables. I’ve seen more ignoramuses piss away more hours trying to get that shit to work… When it could have just been done, reviewed, fixed and issued in half the time.

Maybe it’s because all the people I’ve seen try and sell me on them were dipshits, but fuck pivot tables.

Also, x2 on the shitty import wizard of excel. [/quote]

Pivot tables are great when you have a lot of data to organize and make basic sense out of (sums, counts, etc.) as long as the data is set up in database format.

If not, I agree it can be a big pain in the ass and it’s just as easy to sort it “manually” and plug in formulas (as long as you know what formulas to use/how to use them).

Tool for a job is all it is. Gotta know what you’re building before you pick the tools for the toolbox.[/quote]

Pivot tables are easy as hell to use. I don’t really see the issue. [/quote]

The issues i’ve run into with using them has largely to do with the structure of the data I’m trying to convert … mainly data cleaning issues. Sometimes I’ll get some shit that has absolutely no structure and it’s just a bunch of fucking jumbled data that needs to be sorted. Luckily I know a thing or two using logic loops so I can sort that shit out somewhat quickly. Plus when I’m trying to marry two sets of data from different databases, a pivot table is fucking useless until it’s cleaned and uniform.

To really use pivot tables effectively it needs to be in database structure otherwise you’re going to end up pissing in the wind.


#14

I thought the title of the thread was, “Anyone Good With Sportsheets” and I smugly thought to myself, “never had any complaints…”


#15

[quote]polo77j wrote:

[quote]usmccds423 wrote:

[quote]polo77j wrote:

[quote]countingbeans wrote:
Fuck pivot tables. I’ve seen more ignoramuses piss away more hours trying to get that shit to work… When it could have just been done, reviewed, fixed and issued in half the time.

Maybe it’s because all the people I’ve seen try and sell me on them were dipshits, but fuck pivot tables.

Also, x2 on the shitty import wizard of excel. [/quote]

Pivot tables are great when you have a lot of data to organize and make basic sense out of (sums, counts, etc.) as long as the data is set up in database format.

If not, I agree it can be a big pain in the ass and it’s just as easy to sort it “manually” and plug in formulas (as long as you know what formulas to use/how to use them).

Tool for a job is all it is. Gotta know what you’re building before you pick the tools for the toolbox.[/quote]

Pivot tables are easy as hell to use. I don’t really see the issue. [/quote]

The issues i’ve run into with using them has largely to do with the structure of the data I’m trying to convert … mainly data cleaning issues. Sometimes I’ll get some shit that has absolutely no structure and it’s just a bunch of fucking jumbled data that needs to be sorted. Luckily I know a thing or two using logic loops so I can sort that shit out somewhat quickly. Plus when I’m trying to marry two sets of data from different databases, a pivot table is fucking useless until it’s cleaned and uniform.

To really use pivot tables effectively it needs to be in database structure otherwise you’re going to end up pissing in the wind. [/quote]

Agreed.

I think you mentioned SQL, which would work too, but imo it’s no where near as user friendly. SQL queries can be confusing especially at first and changing data is a pain in the ass imo.


#16

[quote]usmccds423 wrote:

[quote]polo77j wrote:

[quote]usmccds423 wrote:

[quote]polo77j wrote:

[quote]countingbeans wrote:
Fuck pivot tables. I’ve seen more ignoramuses piss away more hours trying to get that shit to work… When it could have just been done, reviewed, fixed and issued in half the time.

Maybe it’s because all the people I’ve seen try and sell me on them were dipshits, but fuck pivot tables.

Also, x2 on the shitty import wizard of excel. [/quote]

Pivot tables are great when you have a lot of data to organize and make basic sense out of (sums, counts, etc.) as long as the data is set up in database format.

If not, I agree it can be a big pain in the ass and it’s just as easy to sort it “manually” and plug in formulas (as long as you know what formulas to use/how to use them).

Tool for a job is all it is. Gotta know what you’re building before you pick the tools for the toolbox.[/quote]

Pivot tables are easy as hell to use. I don’t really see the issue. [/quote]

The issues i’ve run into with using them has largely to do with the structure of the data I’m trying to convert … mainly data cleaning issues. Sometimes I’ll get some shit that has absolutely no structure and it’s just a bunch of fucking jumbled data that needs to be sorted. Luckily I know a thing or two using logic loops so I can sort that shit out somewhat quickly. Plus when I’m trying to marry two sets of data from different databases, a pivot table is fucking useless until it’s cleaned and uniform.

To really use pivot tables effectively it needs to be in database structure otherwise you’re going to end up pissing in the wind. [/quote]

Agreed.

I think you mentioned SQL, which would work too, but imo it’s no where near as user friendly. SQL queries can be confusing especially at first and changing data is a pain in the ass imo. [/quote]

Absolutely agree. There’s a reason no one likes to talk to IT/Data guys :slight_smile:


#17

I’d buy a round of drinks for all you guys. Really appreciate the input.

So it appears I have some googling to do as I have no idea what a pivot table is. Literally never heard of it

Also, seems like a lot of you knowledgeable folk think Xcel sucks balls. Is there something else you could recommend? Talking purely in terms of user friendliness here; I’m not using it for anything fancy. Just to say what stock we have, what we sold, where we sold it and how much we made from it.

Also need to do a sheet detailing all the businesses costs, but I imagine if I get the handle on one I’ll be able to get a handle on another.


#18

[quote]Yogi wrote:
Also, seems like a lot of you knowledgeable folk think Xcel sucks balls. Is there something else you could recommend? Talking purely in terms of user friendliness here; I’m not using it for anything fancy. Just to say what stock we have, what we sold, where we sold it and how much we made from it.[/quote]
How are you tracking that stuff right now? Are you even tracking it right now?


#19

[quote]Yogi wrote:
I’d buy a round of drinks for all you guys. Really appreciate the input.

So it appears I have some googling to do as I have no idea what a pivot table is. Literally never heard of it

Also, seems like a lot of you knowledgeable folk think Xcel sucks balls. Is there something else you could recommend? Talking purely in terms of user friendliness here; I’m not using it for anything fancy. Just to say what stock we have, what we sold, where we sold it and how much we made from it.

Also need to do a sheet detailing all the businesses costs, but I imagine if I get the handle on one I’ll be able to get a handle on another.[/quote]

Excel is probably the most user friendly software you can use for this type of thing, imo. It’s not that bad and like Bean’s said, if it isn’t that much data you can probably just use the sort or filter functions or even the sub-total function. If you’re going to have 1,000s of rows and/or 50+ columns than, imo, the way to go is pivot tables. I prefer pivot table because you aren’t manipulating the data itself only the view of the data. You can very easily ruin the integrity of your data when you fool around with it where as you don’t have to worry about that when using pivots.

You might want to look into Google doc’s too since it’s free and the spreadsheet doc is basically Excel (not as nice).

You could also look into using a database like Access, but that is probably over kill.


#20

One other thing, your local community college probably offers an excel class or two (or 5 really) that’s probably really cheap and worth while if you’ll be using it more and more.