Learn Microsoft Excel Reporting

Is 2007 Better Part 3

2011-09-14T05:38:00.000-07:00

Are you fond of using shortcuts in excel? 2007 is the place to be for you. Why is that ? Its easy just press the alt button and discover the world of possibilities with 2007. It is important to go through the TABs before you start using the 2007 shortcuts. For example in case you want to turn the calculations of excel to manual you need to know in which TAB does it have that option. Its in the formula TAB
In order to use the shortcuts in 2007 it is essential to do some homework which you can start by spending some time browsing thru the tabs and knowing what lies where

Is 2007 better Part 2

2011-09-13T06:40:00.000-07:00

If we look at the benefits of 2007 there are a lot of things that have been taken care of. Thats the speciality of this version as it takes into consideration the limitations of the all earlier versions. As a MIS person I always felt that excel is limited in terms of data and the resources it takes. With 65000 rows and 256 columns one may feel its enough however if you are a hard core MIS guy you would always feel its less as times goes by.
For those of you 2007 comes as a relief with increase in number of rows and columns.

IS 2007 better Part 1

2011-09-11T19:57:00.000-07:00

If you look at the interface of 2007 it looks rather vibrant and vast. The need for going into options for doing any action is very limited as all options are available in the toolbar right from formatting of cells to removing gridlines.Also all those who are used to 2003 shortcuts need not worry. Excel 2007 has the capability of working on 2003 shortcuts and it clearly states that you are using a 2003 shortcut

Whats Best in 2007 as compared to 2003

2011-09-10T23:46:00.000-07:00

Well everyone must be wondering why some of them are still working on 2003 and not 2007 ?
The answer is quite simple. Not every corporate has license to 2007 rather are happy with 2003 as its one of the most stable versions of MS Office and is compatible with all OS and low on resources.
So whats different in 2007 ? Is it just the interface or looks or much more.
Keep following this page for more

How to Present using Excel?

2011-06-27T01:07:00.000-07:00

Excel is not only used to create dashboards or MIS. It can be used as a valuable tool to present your views. So how can you do this?

Its all about what you want to present? Who is your audience? What is the end result you are expecting out of this presentation? How valuable and important is this presentation to you?

These are some of the questions you should be asking yourself before you can create something in excel.

I agree that Powerpoint is the tool which is used for presentations but excel is equally useful.

The final outcome can go in PPT but the base design can be on excel

Dual Conditional Formula

2009-01-31T08:53:00.000-08:00

These formula are simpler than the "IF" formula

1. COUNTIF: Syntax =countif(range,criteria)

This formula can be used in case you want to count number of specific items in a range For example you have a list of fruits and you want to count of them how many are apples. This formula makes it simple. The formula is countif(Range,"Apples")

In place of Apples one can also specify a cell values. So if you want to know how many ones are there in the range of numbers this formula is easy. Also you can find the count of a specific value in the list to know whether it is distinct or no in the list

2. SUMIF:

Syntax: =sumif(range,criteria,sumrange)

The sum if formula is very useful if you have to add specific values corresponding a particular range. So you have two lists ---in one column you have list of managers(list1) and another number of sales done(list2). Now you have master list of managers and you want to find from the list how many sales did each manager did. The formula and method is easy. =sumif(list1,"manager1",list2)

Try these formula they are simple to use

CONDITIONAL Formula

2009-01-08T09:40:00.000-08:00

Here comes the complex formula

The conditional formula means a formula which can work on conditions specified by the user. In case the condition does not match it will give the result of the second condition

The conditional formula is "IF"

Syntax =if(logical test,[valueif true],[valueiffalse]

Explanation: Logical test is the condition

Value if True means if the condition is satisfied this formula will give the result specified in the formula as true else value if false

Eg: I want to put a formula that if the value of two cells is same then I must get result as "YES" else "NO"...The formula is =IF(C2=B2,"YES","NO")

This If formula is very complex as it can be merged with any excel formula

The three components of this formula can be replaced by any formula

Eg: =IF(COUNT(C2:C10)=4,"YES","NO")

This formula means it first counts the numbers in range C2-C10 and if the result is 4 it gives YES else a NO

Eg: =IF(SUM(C2:C10)=4,"YES","NO")

This formula means it first adds the numbers in range C2-C10 and if the result is 4 it gives YES else a NO

We will learn more on this

COUNT Formula

2009-01-08T09:37:00.000-08:00

The count formula is very simple and syntax is =COUNT(C2:C10) where C2 and C10 are the range of the cells . However this formula will only calculate the numbers in the range and will not consider the alphabetic characters

How to write SUM formula

2008-12-28T07:24:00.000-08:00

This is one of the most simple excel formula

SUM - used to add numbers

SYNTAX: =SUM(NUMBER1,NUMBER2,.................NUMBERn)

Each number can be specified in cells and in the systax number1 = cell

eg: = sum(A2,B3,D1) will add the numbers in these cells

You can also use combination of numbers annd cells

eg: =SUM(8,C3) will add the number in cell C3 to 8

You can also specify ranges in this formula

eg: =SUM(A6:I6) will add numbers in cells A6,B6,C6,D6,E6,F6,G6 and I6

Now you can use a combination of all above

eg: =SUM(E4:E9,C3,8,H3:I5,2,7)

Here we have added 2 ranges, one cell and 3 numbers.

Such a simple formula with so many combinations possibel makes SUM formula very useful in excel

RECAP FOR BEGINNERS

2008-12-15T08:31:00.000-08:00

Thank you everyone for your feedback and ideas. I understand that each the average level of understanding of excel for users is beginners. I am doing a recap of whats done so far and in case have any questions put in your comments or mail me at vmani32@yahoo.co.in

The best way to learn excel is to open a spreadsheet and do all sorts of experiments
Opening a spreadsheet is simple Start-->Run-->Excel {Enter}
Spreadsheet generally opens with 3 sheets (TABS). This can be changed in options
Depending on the version of excel the number of rows and columns will vary.
What you see in the sheet are tiny cells which can be formatted to your need and desire
This gives extensive flexibility to excel and one can present reports in varied formats
Excel also gives you the flexibility of store huge amount of data. However the file size gets huge and you need a good RAM to work
You can learn the tricks of formatting from the previous posts by referring to the dummy report at this link https://www.yousendit.com/download/TTZtYnU4NDI5NVZMWEE9PQ
This report will help a lot
Excel reportin g is one of the best forms of reporting available which is cheap and does not invlove any major training
Other systems like SQL, Access, Oracle etc need a lot of technical training. In future I will give you some hints on SQL. May be I can circulate some ebooks
From the next post I will start sharing one formula every two days
Please read all the previous posts carefully before going into formulae

Lesson 3.Formatting Spreadsheet Part 4

2008-12-05T08:45:00.000-08:00

This is the last part of this lesson where we summarise

The trick to get a well formatted spreadsheet is try and look
Give it a shot and find for yourself
Some of theings we studied was how to prepare a dull worksheet to a presentable report
Tools like grouping, hiding cells and column, merging cells, filling colurs are some of them
Using various colour combination of patterns a lot of life can be put in reports
Hiding rows and columns help the user to display only the part which he wants to present
Grouping is user friendly eg: A person wants to present regionwise sales he may do so but he can provide a group inside which there would be citywise sales. So if the user wants to know that he may do so by ungrouping them
If there are calculation in the spreadsheet which have to be hidden or protected then cell protection is useful as it preserves the report from being changed

Lesson 3.Formatting Spreadsheet Part 3

2008-11-23T04:57:00.000-08:00

*****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

After grouping of rows and columns we now move on to filling coulours in cells. Ed Cell D18
You can fill coulours by two ways - Right click on the cell and format Cells or use the fill toolbar on the top
Lets see what are the options we get when we right click on a cell
There are six tabs that one gets when right click on cell
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

2008-11-22T02:46:00.000-08:00

*****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

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
Sometimes merging is used in the title or sometimes when a heading has two or more topics or subtopics in them
Merging helps to highlight the name of the report and shows a span of data. A few examples are Cell H17, H29, F26,etc.
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
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
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
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.
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
Similarly you can do this for columns

Lesson 3.Formatting Spreadsheet Part 1

2008-11-19T02:58:00.000-08:00

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

Keep the dummy report open while reading this
I will be using cell references to make you learn this. In case of any confusion read the previous posts else post a comment
I am using excel 2003 Spreadsheet to teach this as all of us do not have excel 2007
I will not be able to teach the entire formatting at once so this lesson will be split into various parts
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

2008-11-14T23:35:00.000-08:00

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

2008-11-13T02:22:00.000-08:00

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

2008-11-10T03:30:00.000-08:00

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)

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.
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.
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

2008-11-07T08:00:00.000-08:00

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

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.
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.
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.
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).
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.
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

2008-11-04T23:33:00.000-08:00

There are various ways to launch excel

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.
Click the Excel 2007 shortcut button on the Windows Quick Launch toolbar.
Double-click an Excel workbook file in any folder on any drive to which your
computer has access
Double-click the Microsoft Excel 2007 program shortcut icon on your
Windows desktop
Enter Start --> RUN and type excel and enter

Lesson Number 2 - Data Entry in Spreadsheet - Intro

2008-11-04T23:27:00.000-08:00

We will be covering the following in this Lesson

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

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

2008-11-02T00:20:00.000-07:00

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.

Lesson 1 - Chapter 1 - Intro

2008-10-31T10:02:00.000-07:00

The Excel 2007 interface has been redesigned compared to the older versions of Excel we’re all used to. In place of the old pull-down menus, Excel 2007 now relies primarily on theRibbon, a block of commands displayed at the top of the screen and divided into distinctblocks called tabs. All that’s left of the old pull-down menus is the pull-down menuopened with the Office Button, which replicates most of the File commands. Also, inplace of the many toolbars of previous Excel versions, Excel 2007 offers a single toolbarcalled the Quick Access toolbar.The exercises in this first chapter are designed to get you familiar with the new Excel2007 interface. As a result of doing these exercises, you should be comfortable with allaspects of the display screen and the command structure and ready to do all the rest ofthe exercises in this book.Identifying the Parts ofthe Excel Display ScreenBefore you can start using Excel 2007, you have to be familiar with its display screen

Lesson Number 1 - Creating Spreadsheets

2008-10-29T21:44:00.000-07:00

Lesson Number 1 is made up of five chapters designed to give you practice in all the spreadsheet basics, all the way from starting Excel to editing a completed spreadsheet

introduces you to the new user interface in Excel 2007 in the form of the
Office menu, the Ribbon, and the Quick Access toolbar.
Enables you to practice entering spreadsheet data.
Runs you through formatting spreadsheet data.
Gives you training in all aspects of printing the completed spreadsheet.
Gives you plenty of experience with making modifications to the completed
spreadsheet.

Some Basics to learn and follow

2008-10-29T09:32:00.000-07:00

In order to each one of us to follow this I will put one tutorial every day
Starting with simple stuff to advanced calculations 1 by 1 everyday

Introduction

2008-10-29T08:39:00.000-07:00

This is an introduction post for all you excel lovers. I am starting this blog to help thousands of people who wish to know excel indepth. I always faced this challenge when I started excel reporting 4 years back. Today I leadthe Global Reporting Team for a Multinational company. All this in 4 yrs. Imagine I did not know anything of excel 4 yrs back .....Its been a long journey....I want to share my learnings with everyone.
Also I want to help others to learn excel and help them excel in life.
This blog is aplatform for us to meet and help each other