Pivot tables - take it easy

Problem

I have data and I need to get some useful information from them. In this case I want (using pivot table) to know, how many Fords is in the list and how much are they together.

Solution

We will work with this table:
Table to download

I will start by clicking anywhere into the table - it is not necessary to point out any part of the table. Then I will click on "Insert" and "Pivot Table".

In this dialog I don´t have to change anything, I just click on "OK". 

I have created new sheet with the pivot table by doing this.

Notice the right column wit menu - above are in rows names of the columns. As they will be moved into the table, the pivot table will be modified. 

I wanted to know, how many Fords is in the list and how much are they together. I will do it so as I will get a table with sums of prices for all producers - Ford including.

"Ford" is on of the producers. That means I will drag "Producer" from the right rectangle to the rectangle "Drop Row Fields Here" in the table.

Now there is the list of producers in the rectangle, where I dragged it.

Now I need to know, how much cars of these producers are. I will drag "Price" into "Drop Data Items Here".

Now I see, how much are the cars separated by producers.

Now I can see, how many of cars is in the list - separated by producer.

I will double click on "Sum of Price" and in menu I will change "Sum" to "Count".

And that is all.

And here is the video containing this tutorial. 

Are there any questions? Write them below.

VLOOKUP, SLOOKUP

Problem

I need the values from the right table fill into the left one.

In the sample table I need to fill the column "Price per item" by prizes from the small table on the right side (in this sample I am not interested in the column "Revenue at all" and I will pretend it does not exist).
For example to item "chocolate" I want to write the price 12, which is related to chocolate.
I can do it manually here, but it is impossible in case of large table.




Solution
Function VLOOKUP will help me.
I will write into F5:
=VLOOKUP(B5;H7:I11;2)
because:

• All functions start with "="
• "VLOOKUP" is the name of the function
• "B5" because there is the word "chocolate" in B5 - the word, used by Excel to find the relevant price
• "H7:I11" because in this area is the small table situated (table, from which the values are taken) 
• "2" because from the small table we need to get the value, which is in the second column (column "Price per item")

When I want to fill up the function form, it looks like this:

Before I can the function, written into F5, expand to other rows, I have to complete it with absolute cell references.

=VLOOKUP(B5;$H$7:$I$11;2)

I made absolute the second cell reference, referring to the table, from which are the values taken. It is because this table will stay stable for all rows and it is not supposed to be changed by expanding.

And here is the result (after easy completing of the "Revenue at all" column).

Notes:

• If the table, form which are the values taken, would be transponed (just like the table right beside the final table), we would use function SLOOKUP instead of function VLOOKUP and in the third parameter there would be the number of row instead of number of column (however, in our example are both "2")
• How to remember names of this functions? "V" is like "vertical" and "H" is like "horizontal".
• These function are similar to "joining" queries in databases, which we can not use in Excel usually.
• If this function does not work properly, arrange the values in small table according to alphabet.

If you want to work with the sample table, download it here.

Identical width of columns

Problem

There are two columns with different width. I need them to be identically wide.

Solution

I´ll select columns by clicking on their headings, which means on letters A, B, C...

If they are side-by-side (for example A and B or B and C), it´s easy. If not, I´ll click on the first one, then push Ctrl and click on the second one (and third one and so on...)

Then I´ll modify the widht of one of them - it does not matter which one - by dragging its right border in heading (between letters).

And, since I selected more of them, ALL columns will modify its width to the same size.
The same with rows...