Posts Tagged ‘Microsoft Excel’

The First Encounter with Microsoft Excel – Part III

July 17, 2010

Continuing the lesson from Part II:

Khawla wants to add a title above the table in the center. She selects the cells in Row 2 that are exactly above the table …

Microsoft Excel 2003: Adding a Title

… and merges them. Then she writes inside “Personal Balance Sheet”, deselects the merged cells, right-clicks, selects Format Cells, clicks on the Font tab and makes the following changes:

Microsoft Excel 2003: Editing the Font

Clicking OK, the title looks as follows:

Microsoft Excel 2003: Adding a Title

Now, after finishing the first part of the table for year 2010, Khawla wants to add the part of the table for year 2011. She selects 2010 months …

Microsoft Excel 2003: Select 2010 Months

… and drags the mouse 12 cells down …

Microsoft Excel 2003: Dragging Down

(more…)

Using the Ctrl Button to Select Non-Adjacent Cells

July 5, 2010

Using the Ctrl button on the keyboard, you can select multiple non-adjacent cells that can be far away from each other and you have to use the scroll bar to go down to select them.

Let us take the file named “Example 01.xls” from a previous post as an example:

(1) Open Example 01.xls

(2) Click cell “B4”:

Microsoft Excel 2003: Using Ctrl Button to Select Non-Adjacent Cells

(3) Press the Ctrl button and click cell “B21”:

Microsoft Excel 2003: Using Ctrl Button to Select Non-Adjacent Cells

(4) Scroll down, …

Microsoft Excel 2003: Scrolling Down

… press Ctrl, and click cell “B38”:

Microsoft Excel 2003: Using Ctrl Button to Select Non-Adjacent Cells

And this is how you select a number of non-adjacent cells.

Finding Cell Name in Microsoft Excel

July 4, 2010

It is very easy to find the name of a cell in a Microsoft Excel sheet by reading it in the Name Box:

Microsoft Excel 2003: Name Box

But, let us suppose that the formula bar is not displayed. Let us remove it by clicking Formula Bar found under the View menu:

Microsoft Excel 2003: Formula Bar

The name of a cell in a Microsoft Excel sheet is determined by its position in a column relative to the row it is in. For example, cell “B3” is found in column “B” in row “3”:

Microsoft Excel 2003: Cell Name

And cell “D24” is found in column “D” in row “24”:

Microsoft Excel 2003: Cell Name

To make sure, click the View menu and then click Formula Bar, and the Name Box is displayed:

Microsoft Excel 2003: Cell Name

Freezing Table’s Titles Row in Microsoft Excel

July 1, 2010

Imagine you have a very long table in a Microsoft Excel sheet, such as the one below:

Microsoft Excel 2003: Freezing Panes 1

And imagine that you want to edit entry No. 057:

Microsoft Excel 2003: Freezing Panes 2

If there are many columns, you will be confused as to what to change and what the data are.

You can solve this problem using the Freeze Panes command found under the Window menu:

Microsoft Excel 2003: Freezing Panes 3

To freeze the title row, click the row below it …

Microsoft Excel 2003: Freezing Panes 4

… and then click Freeze Panes in the Window menu. The rows above where you selected will be frozen:

Microsoft Excel 2003: Freezing Panes 5

Note the thin black line under the row containing the titles. It means the rows above this line will not move when you scroll down and up in the sheet.

Similarly, if the titles are in a column, click the column to its right and freeze it.

To unfreeze, click Unfreeze Panes under the Window menu:

Microsoft Excel 2003: Freezing Panes 6

I attached the file for you to experiment with it.


Attachment: Example 01.xls

Dragging in Microsoft Excel

June 28, 2010

Open Microsoft Excel and try the following:

(1) Click any cell. Note the black border:

Microsoft Excel 2003: Cell's Black Border

Move your mouse around the black border and note the black cross which appears only in the lower right corner:

Microsoft Excel 2003: Cell's Black Border's Black Cross

(2) Write something inside the cell:

Microsoft Excel 2003: Writing in the Cell

(3) Drag outside the cell up or down several cells away …

Microsoft Excel 2003: Dragging the Cell Outside

… and release the mouse. You will see the cell copied to the cells you dragged into:

Microsoft Excel 2003: Dragging the Cell Outside - the Copies

(4) Write something in another cell …

Microsoft Excel 2003: Writing in the Cell

… and click any cell outside to deselect it:

Microsoft Excel 2003: Deselect the Cell

Click the cell you wrote inside again and drag to the inside by clicking and moving your mouse from the lower right corner of the border …

Microsoft Excel 2003: Dragging the Cell to the Inside

… release the mouse and the content will be deleted:

Microsoft Excel 2003: Cell's Content Deleted

(more…)

The First Encounter with Microsoft Excel – Part II

June 25, 2010

We continue our story from where we stopped last time.

Khawla was working on creating a Personal Balance Sheet to control her expenses and to help achieve her goal of accumulating at least AED100,000 in her bank account by the end of 2011. She drew a table for the balance sheet on a piece of paper and tried to recreate it beautifully in Microsoft Excel. She was able to draw the row containing the columns’ titles and here is a part of it:

Microsoft Excel 2003: Cells With Borders

“Since July, 2010 is coming soon, let me add it first in the table.” Khawla wrote “July” in Cell C5, and moved down to Cell C6 using the Down Arrow in her Keyboard and wrote “August”. And she continued until adding December. And here is how she got so far:

Microsoft Excel 2003: Adding 2010 Months

Again, she wants the months centered in the cells. To be exact, everything she is going to write later should be centered. “If there is a way with which the data will get centered beforehand!” Khawla looked at the sheet and noticed something. She noticed that there is a small rectangle between Column A and Row 1 …

Microsoft Excel 2003: Selecting All Cells

… and she clicked it. All the sheet cells have been selected:

Microsoft Excel 2003: All Cells Selected

She tries to right-click anywhere in the sheet and the menu containing Format cells appears:

Microsoft Excel 2003: All Cells Selected

(more…)

Microsoft Excel Helps in Setting Goals

June 23, 2010

In a previous post, we know that Khawla has a goal: to accumulate AED100,000 in her bank account by the end of 2011. Her only source of income is her salary. Obviously, she can achieve this goal by limiting her monthly expenses (cash outflows).

There is a tool in Microsoft Excel that can help her set her goal. This tool is called Goal Seek which can be found under the Tools menu:

Microsoft Excel 2003: Tools Menu

Assuming that Khawla set her goal for the year 2011, and that her monthly salary is AED10,000, then in a year she can accumulate AED120,000 (10,000*12). But, since she incurs monthly expenses, she sets her goal to accumulate at least AED100,000. How much must her expenses in 2011 be if she wants to accumulate AED100,000 by the end of the year?

Using Microsoft Excel, let us draw the following table:

Microsoft Excel 2003: Inflows, Outflows, and Goal Table

In the table:

(1) Inflows = the accumulated monthly salary in 2011 = AED10,000*12 = AED120,000

– We can write in the empty cell next to “Inflows” the following: =10,000*12
Microsoft Excel 2003: Inflows, Outflows, and Goal Table
– We will call this cell “A“.
– Microsoft Excel will do the calculation. We hit Enter in the keyboard to read the answer.

(2) Outflows = total monthly expenses in year 2011

– Leave the cell to its right empty. Goal Seek will later write the answer for you.
– We will call this cell “X“.

(3) Goal = Inflows – Outflows = A-X = AED120,000-X = AED100,000

– We must define this equation in the cell to the right of “Goal”. Write =, then select the cell A and hit in your keyboard and then select X, and hit Enter.
Microsoft Excel 2003: Inflows, Outflows, and Goal Table

Without using Goal Seek, we can find X. It equals to AED20,000.

Now, in Microsoft Excel, click on Tools from the menus bar and select Goal Seek. You must see the following command window:

Microsoft Excel 2003: Goal Seek Command

Set cell: is our Goal cell. Select it from the Goal Seek command window by clicking on the icon next to Set cell:

Microsoft Excel 2003: Goal Seek Command

… and then select it and hit Enter:

Microsoft Excel 2003: Goal Seek Command

The goal is to accumulate AED100,000. We write 100,000 in the box next to To value:

Microsoft Excel 2003: Goal Seek Command

By changing cell: is cell X, the Outflows. We select it, hit Enter, and then clicking on OK. Microsoft Excel found the answer:

Microsoft Excel 2003: Goal Seek Command

Create and Edit Your Spreadsheets Online with EditGrid

June 21, 2010

EditGrid is a free service offering online spreadsheet editor for you to create, import, and edit Microsoft Excel sheets. You can collaborate with others in working on the same file and chat online.

It is good if you are working on a computer that lacks Windows Office.

The Maximum Number of Rows in a Microsoft Excel Sheet

June 20, 2010

Have you tried to find the exact number of rows in a Microsoft Excel sheet but found it difficult to scroll down since it appeared to you it was an infinite process?

There is a quick way to know the exact number of rows in a new Microsoft Excel sheet:

1. Open Microsoft Excel.

2. Select all the cells in the sheet by either using the shortcut (Ctrl + A) or by clicking on the small rectangle between Column A and Row 1:

Microsoft Excel 2003: Selecting All Cells

3. Right-click on that rectangle and you should see the following menu:

Microsoft Excel 2003: Finding The Maximum Number Of Rows 1

4. Click on Create List…:

Microsoft Excel 2003: Finding The Maximum Number Of Rows 2

You will see the following command window:

Microsoft Excel 2003: Finding The Maximum Number Of Rows 3

Can you see what is written in the text box? It is written “=$1:65536”. It means “Select the range from Row 1 to Row 65536”.

5. Click OK.

6. Go to the end of the sheet using the vertical scroll bar. Easy now, is not it? What is the number of the last row you see?

Microsoft Excel 2003: Finding The Maximum Number Of Rows 4

7. Try to insert a new row by clicking on Row 65536, right-clicking on it, and then click on Insert:

Microsoft Excel 2003: Finding The Maximum Number Of Rows 5

You cannot! Insert is dimmed which means it is impossible to add another row.

There is a quicker way to go to the last row! Hold the scroll bar and move down swiftly:

Microsoft Excel 2003: Scrolling Down

So, the maximum number of rows in a Microsoft Excel sheet is 65536. Why this particular number of rows? I have no idea.

Fun With Excel: Fish Following Mouse Pointer

June 19, 2010

Fish Following Mouse

Here is a nice flash embedded in a Microsoft Excel file. There is a fish that follows your mouse pointer.

Credits: Unknown

Microsoft Excel Online Help & How-To

June 18, 2010

If you are using Microsoft Excel version 2003, 2007, or 2010, and if you want some help during your Microsoft Excel work, go ahead to Microsoft’s Excel and How-to page.

Do not forget practice.

The First Encounter With Microsoft Excel – Part I

June 17, 2010

The 2003 Microsoft Excel interface is quite simpler than that of the 2007 version:

Microsoft Excel 2003 Interface

(Click to enlarge)

Here is a snapshot of the menus bar in the 2003 version of Microsoft Excel:

Microsoft Excel 2003 Menus Bar

(Click to enlarge)

Open Microsoft Excel. Select a range of cells. Note the shape of your mouse pointer which is a big white plus sign. It means you are going to select cells. Press Data in the menus bar and a drop-down menu appears:

Microsoft Excel 2003 Data Menu

Choose the Sort command. You will get an error message since you are working on a new empty sheet:

Microsoft Excel 2003 Error Message Due to Empty Sheet

So, you see, you have to fill cells with data before using some commands, such as Sort. But, if you want to make a ready-to-use formatted table, you can. Let us take the following issue as an example.

(more…)