Lesson 3.Formatting Spreadsheet Part 3

*****Keep the dummy report open while reading this*****In case you don't have the dummy report mail me at vmani32@yahoo.co.in with Subject as Dummy Report

1. After grouping of rows and columns we now move on to filling coulours in cells. Ed Cell D18
2. You can fill coulours by two ways - Right click on the cell and format Cells or use the fill toolbar on the top
3. Lets see what are the options we get when we right click on a cell
4. There are six tabs that one gets when right click on cell
5. Number, Alignment, Font, Border, Patterns and Protection

• Number: This category is used to specify the format of the contents of the cell which can be classified as general or number or currency etc. Based on the content you can choose
• Alignment: This is to align the content of the cell. You can also change the angle of the content
• Font: Here you specify what will the font of the cell content be along with the size
• Border: Here you can decide the pattern of the border with the colours of the border for the cell
• Pattern: Here you cn decide the colour to fill the cell with. You can also specify certain design to fill the colour
• Protection: Has 2 options locked and hidden. Locked is when no one can make changes to the cell if worksheet is protected( Will discuss this in later lessons). Hidden means the cell formulae are not displayed

Lesson 3.Formatting Spreadsheet Part 2

*****Keep the dummy report open while reading this*****
In case you don't have the dummy report mailme at vmani32@yahoo.co.in with Subject as Dummy Report

1. Go to Cell D14 ( By now you should be familiar with this convention which means Cell Column D and row 14). You will notice this cell has been joined to many other cells. This is called merging of cells which is used essentially to make your report presentable
2. Sometimes merging is used in the title or sometimes when a heading has two or more topics or subtopics in them
3. Merging helps to highlight the name of the report and shows a span of data. A few examples are Cell H17, H29, F26,etc.
4. Next on the top left corner of the sheet you will notice two numbers 1 and 2 and along the rows you will find a line which is binding rows
5. Try clicking on either 1 or 2 and you will find row getting hidden or visible. This is called grouping. This is how it is done
6. Select a group of rows ( Entire Row) and then Data --> Group and Outline --> Group. By doing this you indicate that all the grouped row as one family
7. Generally it is practiced that in a report the final result is shown and details are hidden by the group. So incase anyone wants to view it he can just expand the group by clicking the + sign outside the group.
8. The top 1 and 2 are indicative of the level of grouping as you can have multiple grouping. In excel 2003 you can have 8 levels of grouping. To expand all the groups you can use these level indicators. Else just click on the + sign next to the group to expand or collapse
9. Similarly you can do this for columns

Lesson 3.Formatting Spreadsheet Part 1

Have you downloaded the dummy report from the link in the previous post. If not download it right now and save it. In case you face an issue with the link post a comment and I will reload the file for you
Things to remember before we start

1. Keep the dummy report open while reading this
2. I will be using cell references to make you learn this. In case of any confusion read the previous posts else post a comment
3. I am using excel 2003 Spreadsheet to teach this as all of us do not have excel 2007
4. I will not be able to teach the entire formatting at once so this lesson will be split into various parts
5. Also there are various shortcuts of formatting. I am not discussing all of them as it may confuse you

Lets start with Part 1

• When you open the dummy report ( Hereafter referred at DR) you can view various name titles and colours
• Lets start from the topmost. You will see that the row starts from 14 instead of 1. Its not an error but a way to hide unwanted rows
• To unhide do CTRL+A then right click on the row and unhide. Or you can click on the point where row 1 and colum 1 meets and then right click on the row and unhide
• The DR is an classic example of a formatted spreadsheet. I have tried to use as many types as I can in this report
• Once you did above you will notice that only row were unhided and columns not. Repeat the above procedure and instead of clicking on row do it on column and unhide
• Now you have a completely shown sheet
• In columns A,B,C you will send lot of figures which are actually calculations. But while presenting the report I will shown only the output and not calculations
• You can change the width of a column and height of a row by right cliking on them and changing column width and row height to make your report look better

Lesson 3: Formatting your Spreadsheet

• Now in this lesson we will learn how to format our spreadsheet. Formatting is important from a view that when presenting the data you need to present it in a professional way. The are various tools to format like colours, merging,etc
• I will later upload a link to this where you can view a sample file which is made in excel but well formated and presented
• Along with the link I will then give tips for formatting based on that file so that you can refer to it while reading the post
• The link to the file is https://rcpt.yousendit.com/625402216/43badffee249d17c333dfc5d2a6afa81
• Download the report and see the way it is presented
• I will explain it in the next post

Lesson 2 Part 1.4 Adding TABS to existing worksheet

• Whenever you open a new sheet in excel by default it opens a blank workbook with 3 TABS ( Sheets). In case you want to add more sheets you can do ALT+I+W which will insert a new TAB. Or SHIFT+F11 is also a function for it or right click on TAB and insert
• In case you want to change the default option of 3 TABS in a worksheet then go to TOOLS-->OPTIONS-->GENERAL-->Sheets in new workbook. Change to default setting to how much ever sheets you want.
• To move from one TAB to another rather than using mouse everytime you can use CNTRL+Pageup/Pagedown
• The TAB names are SHEET1 SHEET2 .......by default. To change it right click on the TAB name and rename. Shortcut os ALT+O+H+R
• You can also change the colours of the TAB. This helps to distinguish data very easily. To change the colours right click on the TAB name and TAB colur. Shortcut ALT+O+H+T
• In case you want to create copy a partuclar TAB again you can do that by right click on the TAB. Similarly you can delete TABS as well.
• You can also hide sheets in your workbook. This feature helps especially when you are presenting data and don't want the audience to look at the calculation or dump data. To hide a sheet go to FORMAT+SHEET+HIDE. Make sure the sheet to be hidden should be active as in the cursor should be on that sheet. Similarly you can unde sheets to check your dump FORMAT+SHEET+UNHIDE

Lesson 2 Part 1.3 - Using Cntrl and Arrow Keys to move on the worksheet

In this part you can practice moving the cell pointer around a blank worksheet and
between data entries with the Ctrl key and the arrow keys in Sheet1 of Book1. (Excel 2007)

1. Press Ctrl+→, Ctrl+↓, Ctrl+←, and Ctrl+↑ in succession to jump the cell cursor from one corner to the next of the entire Sheet1 worksheet. The first time you press Ctrl+→, the cell cursor jumps from cell A1 all the way to cell XFD1. When you next press Ctrl+↓, the cursor jumps from cell XFD1 all the way down to cell XFD1048576. When you then press Ctrl+←, the cursor jumps all the way left to cell A1048576, and from there all the way back up to cell A1 when you press Ctrl+↑. All this corner-to-corner jumping happens because there are no occupied cells in a particular direction, so the cursor jumps right to the cell on each border of the worksheet.
2. Move the cell cursor to cell A18, type Stop, and press Ctrl+Home. Next, press Ctrl+↓.The cell cursor stops in cell A18 rather than A1048576 because A18 is now occupied with the label Stop.
3. Move the cell cursor to cell AB18, type Stop again, and then press Home. Now, press Ctrl+→.This time, the cell cursor stops in cell AB18 rather than XFD18 because AB18 is now occupied.
   In case your excel version is 2003 the row ranges will be 65536 and colum IV

Lesson 2 Part 1.2 - Browsing through the Worksheet

In this section we will learn to go to different parts of the workbook

1. To scroll up and down rows of the worksheet by windows, press the Page Up orPage Down or click the blank area above or below the scroll box in the verticalscroll bar.
2. To scroll left and right columns of the worksheet by windows, click the blank areato the left or right of the scroll box in the horizontal scroll bar.
3. To quickly scroll through rows or columns of the worksheet, hold down the Shiftkey as you drag the scroll box up or down in the vertical scroll bar, or left and rightin the horizontal scroll bar.
4. If you use a mouse with a wheel button, scroll up and down the rows of the worksheetby rotating the wheel button forward (to scroll up) and backward (to scrolldown).
5. If you use a mouse with a wheel button, pan through the rows and columns of theworksheet by clicking the wheel button and then dragging the triangular mousepointer in the direction you want to scroll.
6. In case you want to go to a specific row and colum eg A65000 then you can use the "GO" Function. We have not started using any functions yet its OK. Press F5 and a box appears. Type A65000 in it and press enter. The cursor will move to that cell -- EASY !!

Lesson 2 Part 1.1 - Launching Excel

There are various ways to launch excel

1. Click Start on the Windows XP taskbar and then highlight All Programs andMicrosoft Office before clicking Microsoft Office Excel 2007. If you’re usingWindows Vista, you click the Start button, type ex in the Start Search textbox and then press Enter while the Microsoft Excel 2007 Office 2007 option isselected at the top of the Programs menu.
2. Click the Excel 2007 shortcut button on the Windows Quick Launch toolbar.
3. Double-click an Excel workbook file in any folder on any drive to which your
   computer has access
4. Double-click the Microsoft Excel 2007 program shortcut icon on your
   Windows desktop
5. Enter Start --> RUN and type excel and enter

Lesson Number 2 - Data Entry in Spreadsheet - Intro

We will be covering the following in this Lesson

1. Launching Excel and opening a new workbook
2. Navigate around the workbook
3. Selecting cell ranges in a worksheet
4. Doing simple data
5. Using AutoFill to create data series and copy formulas
6. Saving the spreadsheet as an Excel workbook file

Exercise 1-1: Opening the Office Menu and Selecting Its Commands

In this exercise, you get familiar with the commands on the Office menu as you practiceopening the Office menu and selecting some of its commands. Make sure that Excel 2007is running and an empty Sheet1 worksheet is active on your computer monitor
1. Highlight the Office Button (by hovering the mouse pointer over it without clickingthe mouse button) and wait until its ScreenTip appears.The ScreenTip shows you an image of the menu and gives you a brief descriptionof its function.
2. Click the Office Button to open its pull-down menu.Note the commands New through Close in the left menu pane. Also note the ExcelOptions and Exit Excel command buttons at the very bottom of the menu.
3. Highlight the Save As command on the Office menu (but don’t click it).Note all the Save a Copy sub-options that now appear in the right menu pane.
4. Now, click the Save As command on the Office menu to select it.Excel opens the Save As dialog box where you can modify the name, location, andtype of Excel workbook file before saving a copy of it.
5. Press the Esc (Escape) key on your keyboard to close the Save As dialog box.
6. Press Alt+F to open the Office menu again, this time from the keyboard.This time, small letters appear on each command as well as on the sub-optionbuttons attached to the Save As and Print commands. These are the access keysthat you can type to select an option rather than clicking its name or button.
7. Type W to display the Print command sub-options, and then type V to select thePrint Preview sub-option.Excel displays an alert dialog box indicating that there’s no data in the Sheet1worksheet to preview. Note the appearance of the dashes in the Excel worksheetdisplay showing where the pages would be divided.
8. Click OK in the alert box and then press Alt+FI to open the Excel Options dialog box.The Excel Options dialog box contains all the options for changing the Excel programand worksheet options.