Showing posts with label Excel. Show all posts
Showing posts with label Excel. Show all posts

Friday, March 19, 2021

How can we insert an image in the background of our Excel sheet name the two way of inserting an image?

How can we insert an image in the background of our Excel sheet name the two way of inserting an image?

To finish this job, please do with the following steps one by one:

1. Click the Insert > Shapes, and select a rectangles shape, see the following screenshot:

doc-insert-printable-image1

2. Draw a shape as large as you need in active worksheet.

3. The Drawing Tools is active now, please click the Shape Fill button and fill the shape with white.

doc-insert-printable-image1

4. Click the Shape Outline button, and replace the outline color with white as well as following screenshot shown.

doc-insert-printable-image1

5. Then right click the shape, and select the Format Shape item from the context menu.

doc-insert-printable-image1

6. In the Format Picture dialog box in Excel 2010 or Format Picture pane in Excel 2013, please do the following operations:

doc-insert-printable-image1 In Excel 2010:
 (1.) Click Fill option from the left pane;

 (2.) Select the Picture or texture fill option;

 (3.) Click the File button to specify a image to fill this shape;

 (4.) Check the Tile picture as texture option;

 (5.) Change the Transparency to 75% as you need to
 make the background image like watermark.
doc-insert-printable-image1In Excel 2013 and later versions:
(1.) Click Fill & Line button;

(2.) Under the Fill section:

   Select the Picture or texture fill option;

    Click the File button to specify a image to fill this shape;

   Check the Tile picture as texture option;

   Change the Transparency to 75% as you need to make
 the background image like watermark.

7. In the same dialog, then do the following operations:

doc-insert-printable-image1In Excel 2010:
(1.) Click Properties option at left bar;

(2.) Select the Move and size with cells option in Object
 positioning section;

(3.) Check the Print Object option.

doc-insert-printable-image1In Excel 2013 and later versions:
(1.) Click Size & Properties button;

(2.) Under the Properties section:

    Select the Move and size with cells option;

     Check the Print Object option.

8. Click Close button. Then the shape filled with pictures looks like a background image. What's more, it can be printed out when you print the active worksheet. See the following screenshot:

doc-insert-printable-image6

Note: After inserting this background picture, you will not be allowed enter any text within the picture range.


Wednesday, November 25, 2015

Excel - Intro

Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. The current versions at the time of writing this tutorial are 2010 for Microsoft Windows and 2011 for Mac OS X.
Microsoft Excel is a spreadsheet tool capable of performing calculations, analyzing data and integrating information from different programs.
By default, documents saved in Excel 2010 are saved with the .xlsx extension where as the file extension of the prior Excel versions is .xls.

Audience

This tutorial has been designed for computer users who are willing to learn Microsoft Excel in simple steps and they do not have much knowledge about computer usage and Microsoft applications. This tutorial will give you enough understanding on MS Excel from where you can take yourself at higher level of expertise.

Prerequisites

Before proceeding with this tutorial you should have a basic understanding of Computer peripherals like mouse, keyboard, monitor, screen etc and their basic operations.

Excel - Start

This chapter will teach you how to start a excel 2010 application in simple steps. Assuming you have Microsoft Office 2010 installed in your PC, to start excel application, follow the following steps at your PC:
Step (1): Click Start button.

Windows Start Button Step (2): Click All Programs option from the menu.
Windows All Programs Step (3): Search for Microsoft Office from the sub menu and click it.
Microsoft Office 2010 Step (4): Search for Microsoft Excel 2010 from the submenu and click it.
Microsoft Excel 2010
This will launch Microsoft Excel 2010 application and you will see the following excel window.

Excel Window

Excel - Explore

Following is the basic window which you get when you start excel application. Let us understand various important parts of this window.
Explore Excel Window

File Tab:

The File tab replaces the Office button from Excel 2007. You can click it to check Backstage view, which is the place to come when you need to open or save files, create new sheets, print a sheet, and do other file-related operations.

Quick Access Toolbar:

This you will find just above the File tab and its purpose is to provide a convenient resting place for the Excel most frequently used commands. You can customize this toolbar based on your comfort.

Ribbon:

Excel Ribbon Ribbon contains commands organized in three components:
  • Tabs : They appear across the top of the Ribbon and contain groups of related commands. Home, Insert, Page Layout are example of ribbon tabs.
  • Groups : They organize related commands; each group name appears below the group on the Ribbon. For example group of commands related to fonts or or group of commands related to alignment etc.
  • Commands : Commands appear within each group as mentioned above.

Title bar:

This lies in the middle and at the top or the window. Title bar shows the program and sheet titles.

Help:

The Help Icon can be used to get excel related help anytime you like. This provides nice tutorial on various subjects related to excel.

Zoom Control:

Zoom control lets you zoom in for a closer look at your text. The zoom control consists of a slider that you can slide left or right to zoom in or out, . and + buttons you can click to increase or decrease the zoom factor.

View Buttons:

The group of three buttons located to the left of the Zoom control, near the bottom of the screen, lets you switch among excel's various sheet views.
  • Normal Layout view : This displays page in normal view.
  • Page Layout view :This displays pages exactly as they will appear when printed. This gives a full screen look of the document.
  • Page Break view : This shows a preview of where pages will break when printed.

Sheet Area:

The area where you enter data. The flashing vertical bar is called the insertion point and it represents the location where text will appear when you type.

Row Bar

Rows are numbered from 1 onwards and keeps on increasing as you keep entering data. Maximum limit is 1,048,576 rows.

Column Bar

Columns are numbered from A onwards and keeps on increasing as you keep entering data. After Z, it will start series of AA,AB and so on. Maximum limit is 16,384 columns.

Status Bar:

This displays sheet information as well as the insertion point location. From left to right, this bar can contains the total number of pages and words in the document, language etc.
You can configure the status bar by right-clicking anywhere on it and by selecting or deselecting options from the provided list.

Dialog Box Launcher:

This appears as very small arrow in the lower-right corner of many groups on the Ribbon. Clicking this button opens a dialog box or task pane that provides more options about the group.

Excel - Backstage view

The Backstage view has been introduced in Excel 2010 and acts as the central place for managing your sheets. The backstage view helps in creating new sheet, saving and opening sheets, printing and sharing sheets, and so on.
Getting to the Backstage View is easy: Just click the File tab, located in the upper-left corner of the Excel Ribbon. If you already do not have any opened sheet then you will see a window listing down all the recently opened sheets as follows:
Excel Backstage View If you already have an opened sheet then it will display a window showing detail about the opened sheet as shown below. Backstage view shows three columns when you select most of the available options in the first column.
Excel Backstage View 2 First column of the backstage view will have following options:
OptionDescription
SaveIf an existing sheet is opened, it would be saved as is, otherwise it will display a dialogue box asking for sheet name.
Save AsA dialogue box will be displayed asking for sheet name and sheet type, by default it will save in sheet 2010 format with extension .xlsx
OpenThis option will be used to open an existing excel sheet.
CloseThis option will be used to close an opened .
InfoThis option will display information about the opened sheet.
RecentThis option will list down all the recently opened sheets
NewThis option will be used to open a new sheet.
PrintThis option will be used to print an opened sheet.
Save & SendThis option will save an opened sheet and will display options to send the sheet using email etc.
HelpYou can use this option to get required help about excel 2010.
OptionsUse this option to set various option related to excel 2010.
ExitUse this option to close the sheet and exit.

Sheet Information

When you click Info option available in the first column, it displays the following information in the second column of the backstage view:
  • Compatibility Mode: If the sheet is not a native excel 2007/2010 sheet, a Convert button appears here, enabling you to easily update its format. Otherwise, this category does not appear.
  • Permissions: You can use this option to protect your excel sheet. You can set a password so that nobody can open your sheet, or you can lock the sheet so that nobody can edit your sheet.
  • Prepare for Sharing: This section highlights important information you should know about your sheet before you send it to others, such as a record of the edits you made as you developed the sheet.
  • Versions: If the sheet has been saved several times, you may be able to access previous versions of it from this section.

Sheet Properties

When you click Info option available in the first column, it displays various properties in the third column of the backstage view. These properties include sheet size, title, tags, categories etc.
You can also edit various properties. Just try to click on the property value and if property is editable then it will display a text box where you can add your text like title, tags, comments, Author.

Exit Backstage View

It is simple to exit from Backstage View. Either click on File tab or press Esc button on the keyboard to go back in excel working mode.

Excel - Entering the values

Let us see how easy is to enter text in a excel sheet. Hope you are aware that when you start a sheet, it displays a new sheet by default as shown below:
Enter Text Sheet area is the area where where you type your text. The flashing vertical bar is called the insertion point and it represents the location where text will appear when you type. When you click on a box then box becomes highlighted.When you double click the box flashing vertical bar will come and you can start entering data then.
So just keep your mouse cursor at the text insertion point and start typing whatever text you would like to type. I typed only two word "Hello Excel" as shown below. The text appears to the left of the insertion point as you type:
Hello Excel There are following three important points which would help you while typing:
  • Press Tab to go to next column.
  • Press Enter to go to next row.
  • Press Alt + Enter to enter a new line in the same column.

Excel - Move Around

Excel provides a number of ways to move around a sheet using the mouse and the keyboard.
First let us create some sample text before we proceed. Open a new excel sheet and type any data. We've shown a sample data in the screenshot.
OrderDateRegionRepItemUnitsUnit CostTotal
1/6/2010EastJonesPencil951.99189.05
1/23/2010CentralKivellBinder5019.99999.5
2/9/2010CentralJardinePencil364.99179.64
2/26/2010CentralGillPen2719.99539.73
3/15/2010WestSorvinoPencil562.99167.44
4/1/2010EastJonesBinder604.99299.4
4/18/2010CentralAndrewsPencil751.99149.25
5/5/2010CentralJardinePencil904.99449.1
5/22/2010WestThompsonPencil321.9963.68
6/8/2010EastJonesBinder608.99539.4
6/25/2010CentralMorganPencil904.99449.1
7/12/2010EastHowardBinder291.9957.71
7/29/2010EastParentBinder8119.991,619.19
8/15/2010EastJonesPencil354.99174.65
Excel Sample Text

Moving with Mouse

You can easily move the insertion point by clicking in your text anywhere on the screen. Sometime if sheet is big then you can not see a place where you want to move. In such situation you would have to use the scroll bars, as shown in the following screen shot:
Excel Scroll Bars You can scroll your sheet by rolling your mouse wheel, which is equivalent to clicking the up-arrow or down-arrow buttons in the scroll bar.

Moving with Scroll Bars

As shown in the above screen capture, there are two scroll bars: one for moving vertically within the sheet, and one for moving horizontally. Using the vertical scroll bar, you may:
  • Move upward by one line by clicking the upward-pointing scroll arrow.
  • Move downward by one line by clicking the downward-pointing scroll arrow.
  • Move one next page, using next page button (footnote).
  • Move one previous page, using previous page button (footnote).
  • Use Browse Object button to move through the sheet, going from one chosen object to the next.

Moving with Keyboard

The following keyboard commands, used for moving around your sheet, also move the insertion point:
KeystrokeWhere the Insertion Point Moves
Forward ArrowForward one box
Backword ArrowBack one box
Upward ArrowUp one box
Downard ArrowDown one box
PageUpTo the previous screen
PageDownTo the next screen
HomeTo the beginning of the current screen
EndTo the end of the current screen
You can move box by box or sheet by sheet.Now click in any box containing data in the sheet. You would have to hold down the Ctrl key while pressing an arrow key, which moves the insertion point as described here:
Key CombinationWhere the Insertion Point Moves
Ctrl + Forward ArrowTo the last box containing data of the current row
Ctrl + Backword ArrowTo the first box containing data of the current row
Ctrl + Upward ArrowTo the first box containing data of the current column
Ctrl + Downard ArrowTo the last box containing data of the current column
Ctrl + PageUpTo the sheet in the left of the current sheet.
Ctrl + PageDownTo the sheet in the right of the current sheet.
Ctrl + HomeTo the beginning of the sheet
Ctrl + EndTo the end of the sheet

Moving with Go To Command

Press F5 key to use Go To command, which will display a dialogue box where you will have various options to reach to a particular box.
Normally we use row and column number ,for example K5 and finally press Go To button.
Excel Go To Command

Excel - Saving the Sheet

Saving New Sheet

Once you are done with typing in your new excel sheet, it is time to save your sheet/workbook to avoid losing work you have done on an Excel sheet. Following are the steps to save an edited excel sheet:
Step (1) : Click the File tab and select Save As option.
Save As Option Step (2) : Select a folder where you would like to save the sheet, Enter file name which you want to give to your sheet and Select a Save as type, by default it is .xlsx format.
Save Option Step (3) : Finally, click on Save button and your sheet will be saved with the entered name in the selected folder.

Saving New Changes

There may be a situation when you open an existing sheet and edit it partially or completely, or even you would like to save the changes in between editing of the sheet. If you want to save this sheet with the same name then you can use either of the following simple options:
  • Just press Ctrl + S keys to save the changes.
  • Optionally you can click on the floppy icon available at the top left corner and just above the File tab. This option will also save the changes.
  • You can also use third method to save the changes, which is Save option available just above the Save As option as shown in the above screen capture.
If your sheet is new and it was never saved so far, then with either of the three options, word would display you a dialogue box to let you select a folder, and enter sheet name as explained in case of saving new sheet.

Excel - Create Worksheet

Creating New Worksheet

Three new, blank sheets always open when you start Microsoft Excel. But suppose that you want start another new worksheet while you are working on another worksheet, or you closed already opened worksheet and want to start a new worksheet. Here are the steps to create a new worksheet:
Step (1) : Right Click the Sheet Name and select Insert option.
New Sheet Step (2) : Now you'll see the Insert dialog with select Worksheet option as selected from the general tab. Click Ok button
Insert Dialog Now you should have your blank sheet as shown below ready to start typing your text.
Blank Sheet You can use a short cut to create a blank sheet anytime. Try using Shift+F11 keys and you will see a new blank sheet similar to above sheet is opened.

Excel - Copy Work Sheet

Copy Worksheet

First let us create some sample text before we proceed. Open a new excel sheet and type any data. We've shown a sample data in the screenshot.
OrderDateRegionRepItemUnitsUnit CostTotal
1/6/2010EastJonesPencil951.99189.05
1/23/2010CentralKivellBinder5019.99999.5
2/9/2010CentralJardinePencil364.99179.64
2/26/2010CentralGillPen2719.99539.73
3/15/2010WestSorvinoPencil562.99167.44
4/1/2010EastJonesBinder604.99299.4
4/18/2010CentralAndrewsPencil751.99149.25
5/5/2010CentralJardinePencil904.99449.1
5/22/2010WestThompsonPencil321.9963.68
6/8/2010EastJonesBinder608.99539.4
6/25/2010CentralMorganPencil904.99449.1
7/12/2010EastHowardBinder291.9957.71
7/29/2010EastParentBinder8119.991,619.19
8/15/2010EastJonesPencil354.99174.65
Excel Sample Text Here are the steps to copy an entire worksheet
Step (1) : Right Click the Sheet Name and select Move or Copy option.
Copy Sheet Step (2) : Now you'll see the Move or Copy dialog with select Worksheet option as selected from the general tab. Click Ok button
Move Dialog Select Create a Copy Checkbox to create a copy of current sheet and Before sheet option as (move to end) so that new sheet gets created at end.
Press Ok Button
Now you should have your copied sheet as shown below.
Copied Sheet You can rename the sheet by double clicking on it.On double click, name become editable. Enter any name say Sheet5 and press Tab or Enter Key.

Excel - Hiding a worksheet

Hiding Worksheet

Here is the step to hide a worksheet
Step (1) Right Click the Sheet Name and select Hide option. Sheet will get hidden.
Hide Sheet

Unhiding Worksheet

Here are the steps to unhide a worksheet
Step (1) Right Click on any Sheet Name and select Unhide... option.
Unhide Sheet Step (2) Select Sheet Name to unhide in Unhide dialog to unhide the sheet.
Press Ok Button
Now you will have your hidden sheet back.

Excel - Delete Worksheet

Delete Worksheet

Here is the step to delete a worksheet
Step (1) Right Click the Sheet Name and select Delete option.
Delete Sheet Sheet will get deleted if it is empty otherwise you'll see a confirmation message.
Delete Sheet2 Step (2) Press Delete Button
Now your worksheet will get deleted.

Excel - Close Worksheet

Close Workbook

Here are the steps to close a workbook
Step (1) Click the Close Button as shown below.
Close Sheet You'll see a confirmation message to save the workbook.
Close Sheet2 Step (2) Press Save Button to save the workbook as we did in MS Excel - Save Workbook chapter.
Now your worksheet will get closed.

Excel - Open Workbook in Excel

Open Workbook
Let us see how to open workbook from excel
Step (1) Click the File Menu as shown below. You can see open option in File Menu. There are two more columns Recent workbooks and Recent places where you can see recently opened workbooks and recent places from where workbooks are opened.
File Menu Step (2) Clicking the Open Option will open the browse dialog as shown below. Browse the directory and find file you need to open.
Open Dialog Step (3) Once you select the workbook your workbook will be opened as below:
File Menu

Excel - Help





Introduction (Chapter 1)
[Link]
[Link]
  1. Item 1
  2. Item 2
  3. Item 3


MS Excel provides context sensitive help on mouse over. To see context sensitive help for Particular Menu option hover the mouse over the option for some time. Then you can see context sensitive Help as shown below Context Sensitive Help

Getting More Help

For getting more help with MS Excel from Microsoft you can press F1 or by File -> Help -> Support -> Microsoft office HelpHelp from File