S R T ASIA LTD (Excel Tutorials)

5/22/2012 Excel Tutorials-SRT ASIA LTD 1 There are more about MS excel worth learning than one can teach in a whole week. So it’s common to find that excel being constantly underused in any businesses, by any individuals. This set of tutorials is aimed at some practical tricks about excel, but it’s not arranged in a systematic and well-structured manner due to restrictions in time and the author’s capabilities. So basically the each two courses may not be necessarily inter-connected with each other. If you are interested in the way how exactly excel works and all the theories behind it, you are advised to read textbooks or turn to me if I can be of any assistance. OK, Let’s get started. 25/22/2012 Excel Tutorials-SRT ASIA LTD Print Options  Print Header or Some certain Row in Each page. Print comments Sometimes we want the header or the left-most column to appear in each printout pages, when the spreadsheet is very long. Then how we do this trick? Take below sheet for example. We want the first two rows (title) to appear in each page when print out. How to do it? 5/22/2012 Excel Tutorials-SRT ASIA LTD 3 1. Move to “Page Layout” and click “Print titles”. 2. Click encircled icon as below. One is about repeating rows at top that you can specify; the other is about the columns that you want to appear in every page printout. Normally the comment is not printable based on default setting. But you can select and make this happening in this combo box. You can either choose to print the comment the way as it displays in the sheet or at the bottom of the sheet. 5/22/2012 Excel Tutorials-SRT ASIA LTD 4 3. Select the area that you wants to repeat and press “ ”. 4. Click “OK”. Practice on below spreadsheet. 5/22/2012 Excel Tutorials-SRT ASIA LTD 5 Videos. This is a GIF motion picture recording all the steps. It can be properly played in windows XP. But not in Win7 picture viewer. You are suggested to open it with some other picture viewing programs like “QQ影像” or simply open it in IE. 1. Print the header.  Economic Printing. Sometimes you want to print a sheet or a WORD document that’s very lengthy and is going to run many pages. If the document is not quite formal, you can print 2 or 4 or 8, or even 16 pages on one piece of paper to save resources. Also it’s more environmentally friendly. The trick is simple. Print Properties Page per side 5/22/2012 Excel Tutorials-SRT ASIA LTD 6 5/22/2012 Excel Tutorials-SRT ASIA LTD 7 Secure your document  Make your spreadsheet password-protected. Save as Tools General options Choose password 5/22/2012 Excel Tutorials-SRT ASIA LTD 8 5/22/2012 Excel Tutorials-SRT ASIA LTD 9 Viewers have to enter password to open the sheet. Viewers have to enter password to modify the document and save. If this option is checked. Then when viewers open the protected sheet, it will pop up a window like below. 5/22/2012 Excel Tutorials-SRT ASIA LTD 10 Slim down your file Sometimes it do happens that we have got an excel sheet or word document that is very big in size due to many pictures inside. Bulky size makes such files very difficult to send through email transmission. OK, let’s slim down these files. The process is same in all office applications. See below flow chart Select the picture Format Compress picture Options Choose 5/22/2012 Excel Tutorials-SRT ASIA LTD 11 Default setting will make the compressing happen on all the pix in the document. Check this option if you just want the compressing works in this single picture that selected. MIN. compressing Medium compressing MAX. compressing 5/22/2012 Excel Tutorials-SRT ASIA LTD 12 Rounding numbers Sometimes we need to round the numbers to 1 or 2 or three decimals. How to do this more easily? Maybe you will say I can use the “increase decimal” or “decrease decimal button”. Or I can go to “format cells” and set the number as 2 decimals. Or set the value as currency. 5/22/2012 Excel Tutorials-SRT ASIA LTD 13 This would certainly works, at least in one way. But there’s a problem. See above number. Also it’s displayed as 1.35. But it’s actual value is still 1.3456. And it will calculate as 1.3456. See another example. See the problem? 1.37*2=2.74, rather than 2.73. But why? Because it is calculated this way: =1.3656*2=2.7312, then displayed in two decimals, so shorted to 2.73 =1.3656, set format as currency, 2 decimals by default =B1*2 5/22/2012 Excel Tutorials-SRT ASIA LTD 14 There’s another way to avoid it but today we will be focusing on a more intuitive way. And that’s to use formula: “=round” How many decimals you want keep The number that you need to be rounded. Let’s see the same example as above. See what happens if we use round formula. =round(1.3656,2) =B2*2 5/22/2012 Excel Tutorials-SRT ASIA LTD 15 There are two more formulas about rounding number. ROUNDUP ROUNDDOWN As its names suggest, these two formulas work on two opposite ways. One is to round the numbers upwardly and the other, downwardly. So, for instance, =round(12.345,2)=12.35 =roundup(12.344,2)=12.35 =rounddown(12.347,2)=12.34 5/22/2012 Excel Tutorials-SRT ASIA LTD 16 Paste options Apart from regular PASTE, there are several more special PASTE functions that worth a try. Let’s take a look at some useful one. This option can transform the content into a picture in the area that’s pasted to. The picture will be in BMP format and very clear. It can save the trouble of format loss or messy format when trying to copy some cells into another sheet or WORD document. 5/22/2012 Excel Tutorials-SRT ASIA LTD 17 Selected the red-squared area and Press “Ctrl+C” Move to this squared cell and click PASTE-PASTE SPECIAL You can try the other options. 5/22/2012 Excel Tutorials-SRT ASIA LTD 18 Sometimes we want to have some data in different sheets stay in sync with each other. For example in one sheet, you have this data “monthly income=1200” in sheet A. And in sheet B there’s also this data. But actually the monthly income will be updating continuously. So we start to wonder if there’s anyway to keep the data in the different file synchronized with the one we are updating. The answer is Yes and very simple. You simply hv to check this option. There are also many other options as displayed in the picture. Give it a try when you hv time. 5/22/2012 Excel Tutorials-SRT ASIA LTD 19 Date-Filter We do sometimes find us in need of finding a tool to filter all the data in some column based on our desired conditions. In the left there’s a graph. It’s a very short graph but just imagine that it’s a very long table and we want to filter out the rows that has a “税率” at or over 10%. What do we do to realize it? Yes, we need the “DATA-FILTER” function. Select area Data Filter 5/22/2012 Excel Tutorials-SRT ASIA LTD 20 5/22/2012 Excel Tutorials-SRT ASIA LTD 21 We can either pick the options that listed automatically to filter. Or we can use the “number filters” to define more filter conditions. Give it a go! 5/22/2012 Excel Tutorials-SRT ASIA LTD 22 Converting measurement It always happens when we have to convert numbers in one unit to another. For example, meters converted to yard, centimeters converted to inches or feet, Celsius degree to Fahrenheit degree and so on. Today we will be focusing on how to do this without any knowledge about conversion rate. The number to be converted From what unit To what unit 5/22/2012 Excel Tutorials-SRT ASIA LTD 23 Common abbreviation of units 5/22/2012 Excel Tutorials-SRT ASIA LTD 24 幻灯片编号 1 幻灯片编号 2 幻灯片编号 3 幻灯片编号 4 幻灯片编号 5 幻灯片编号 6 幻灯片编号 7 幻灯片编号 8 幻灯片编号 9 幻灯片编号 10 幻灯片编号 11 幻灯片编号 12 幻灯片编号 13 幻灯片编号 14 幻灯片编号 15 幻灯片编号 16 幻灯片编号 17 幻灯片编号 18 幻灯片编号 19 幻灯片编号 20 幻灯片编号 21 幻灯片编号 22 幻灯片编号 23 幻灯片编号 24