Learning Excel

[Stanger00]

The more you use it the better you'll be and finding the shortcuts with hot keys is the best thing to learn first. Minimizing the use of your mouse will save you from hitting ctrl-z while building syntax functions because you will click a random cell on accident.

I don't have much professional experience but for the last year or more used it for all of analytics courses for my undergraduate degree. I even used it for college statistics.

Excel is super powerful and got to use some add-ons in solver that made operational management super easy to calculate supply chain management questions.

Now I use it as a common spreadsheet filled with data I pull from a CMMS and SAP programs to make the information more useable for finding trends, scheduling and parts availability. This is pretty easy stuff as I don't even have to create my own functions to do it because pivot table does it for me.


Sent from my iPhone using Tapatalk

 

[VerySneaky]

Thanks for the feedback guys. Ill start with the basics then once I become more immersed in the job I will better know what functions will help me save time.

Never be afraid to ask for help.

 

[04SVT_COBRA]

Okay, so I "officially" got the job today!

They basically said what they do isn't too complex and, and the only things I really need to spend time knowing are v-lookup and pivot tables, as well as knowing hotkeys.

 

[bigja01cobra]

Learn Access, make a database with the data. Create a nice UI with a search feature and which displays record information with a form.

Create some queries that translate into some nice looking reports, and prosper.

I miss using access to create simple relational databases.

What he said. I have been to many business who are using excel for things that a database would be much nicer and easier to handle.
Access is a great starter to databases.

I suggest you hit up GroupOn and look for places like Horizon Learning Center they have the full Microsoft suite certification lessons and class for like $99 you can learn everything from excel basic to VBA to visio and MS project. And get ceritificates to boost the resume.

 

[R.D.P.]

Pivot tables and vlookups are like the green belt of Excel. You want to get serious and be able to do some stuff that will impress, vba is the way to go.

 

[CobraBob]

I highly recommend Lynda.com for things like learning Excel. You can pay for a month-long license which should be all you need. Here are just a few of the Excel courses offered.
Excel Quick Tips (30 min.)
Excel 2016 Essential Training (8 hrs.)
Learning Excel 2016 (1 hr.)
Excel 2016: Macros in Depth (3.5 hrs.)
Work With Excel Tables (4 hrs.)
Excel 2016: Financial Functions in Depth (2.5 hrs.)

 

[fmylife]

Microsoft Virtual Academy has a couple free courses to learn about Excel. Not sure how much it'll help but it's something.

https://mva.microsoft.com/search/SearchResults.aspx#!q=excel 2013&index=2&lang=1033

 

[Voltwings]

I work with a lot of old people who use Excel as a data entry tool, and not a data "manipulation," or "calculation" tool. I have cut HOURS out of my co workers days by teaching them to automate simple tasks.

My biggest advice:

1. Automate everything you can. This not only helps with speed, be precision. Every single point of human interaction, is a point of failure. For example, don't type things if they can be copied or referenced to prevent typing in a wrong number.

2. Don't hard code anything. If you leave your spreadsheet as mostly formulas, it is an organic sheet that can be changed very easily. If you calculate a number by hand and then just type that number into a cell, you are stuck with exactly that number. If you let excel do the calculations and keep them in your sheet, you have the ability to quickly and easily change things.

3. Save templates / color code / leave yourself notes. I build templates as often as possible, what took my co workers 2 hours takes me 10 minutes. I had to put 3-4 hours into building the template the first time, but now i am much more proficient than they are. Color code!!! I always color code my template cells; Cells that are inputs get one color, cells that are outputs (calculated via a formula) get another... And lastly, make notes on all your sheet. If you, or anyone else, needs to come back and use that template after a week, a month, a year... will you remember how to use it? Will they know how to use it? Notes help.

4. Lastly, excel lists all available formulas in one of the tabs. Google those formulas and see not only how they work individually, but how they can be stacked. I have been working in excel for about 8 years, and even today i am learning new things and new ways to stack formulas to get what i need done. Just know that damn near anything is possible in excel.

 

[04SVT_COBRA]

I have something I need help with...

Our company might have two different packaging options for the same MFGSKU (Manufacturer’s SKU). I have a few clients who do not care which packaging they receive, and we also have agreements that when they purchase from us, they receive the lesser pricing of the two packaging options.

To illustrate, if you look at the first image, you will see rows #2 & #3 have the same MFGSKU. However, they have two different "Carton Types" that are noted in the suffix of our "Company Model".

I want to create a Macro or some type of formula that will search for duplicates of MFGSKU, then combine the two "Net Available (Company Model)" quantities. Then I want to delete the row with the higher price ("Original Package" always has higher price) , and also delete the "Company Model" and "Net Available (Company Model)" columns. The resulting chart will look like the second image.

I can do this manually, but sometime due to the size of the list, it may take me a couple minutes or so.

CLICK HERE TO DOWNLOAD EXCEL FILE

 

[IronSnake]

You'd probably need to start with an IF() statement and build from there. You'd need to function with the decision that you could dictate should B3 and B4 be the same, than (F3+F4).

There's a way to do it and i'm sure with a little bit of time I could handle it. I just don't have the free 30 minutes or so to play with it.

 

[RX1Cobra]

I don't have time to check my thoughts but... First you can do a sumif. Your sumif will be based on the sku and the sum will be net available. This will get you how many you have based on sku but will duplicate the number for any item with the same sku.

I'd copy the sum result and paste it as a value. Sort by cost (lowest first), remove duplicates (reason we pasted as value) based on sku and I think you'll net what you're looking for. Could be an easier way but this is a pretty simple macro if you use macro the record macro function.

 

[04SVT_COBRA]

Thanks guys, I will give those suggestions a go and see what happens!

 

[LogiWorld123]

Having written a predictive simulation of a vending machine based on real sales data in a previous life, you can Google anything.

 

[Stanger00]

Having written a predictive simulation of a vending machine based on real sales data in a previous life, you can Google anything.

This statement here is directly out of business intelligence text books and there are programs already built to handle this types of simulations but when I school you do have to do the arithmatic to determine the outcome of predicted sales of x,y,z products.

Google works for all of this too and YouTube.


Sent from my iPhone using Tapatalk

 

[LogiWorld123]

This statement here is directly out of business intelligence text books and there are programs already built to handle this types of simulations but when I school you do have to do the arithmatic to determine the outcome of predicted sales of x,y,z products.

Google works for all of this too and YouTube.


Sent from my iPhone using Tapatalk

This pulled sales data from an AS/400 sales file and we couldn't get the head programmer to help us, so we made do with what we had.

If I didn't set the import function right (ie filter by date), trying to import a 167GB sales file would all but kill the intranet.

 

[Stanger00]

This pulled sales data from an AS/400 sales file and we couldn't get the head programmer to help us, so we made do with what we had.

If I didn't set the import function right (ie filter by date), trying to import a 167GB sales file would all but kill the intranet.

That's cool. I just remember having assignments similar to what you described .

Writing the instruction and figuring it all out probably wasn't too fun though.


Sent from my iPhone using Tapatalk

 

[2veloce2003]

Practice practice practice is your friend. I used EXCEL extensively in my work and I learned by doing not by reading. Use the help function and use it every time you need to analyze data. Also save time by downloading data from the source and organizing it to your needs.

 

[LogiWorld123]

That's cool. I just remember having assignments similar to what you described .

Writing the instruction and figuring it all out probably wasn't too fun though.


Sent from my iPhone using Tapatalk

Sumif arguments were obnoxious. I wish I still had it, was fun to toy around with. The last revision I made before I quit was to actually put in net profit on each item, and then forecast net profit if we had to go change the price on an item due to supply costs, and would do the whole conditional formatting of red and green if it was worth spending the money to change the price tags out