Excel 2007 Tips & Tricks - Microsoft Excel

Search Home All Forums

Home

Microsoft Offic...

Microsoft Excel

Excel 2007 Tips & Tricks

9/3/2007 9:31 AM

soumya

windowsxperience.wordpress.com/
Joined: 8/8/2006

Posts: 5438

Excel 2007 Tips & Tricks Modified By soumya on 9/3/2007 9:36:06 AM

In this tutorial, you will learn how to work with consolidated data.

Duplicated rows removal

1) Open the Sample.xlsx Sample.

Illustration 1: Sample book.

2) As you can see, rows 7... 9 and 12 ... 14 have the same data. Select the F1 cell.

3) Select the Remove Duplicates command from the Data Tools Group in the Data in the Options Tape.

Illustration 2: Select Remove duplicates command.

4) Select the columns that you suspect to have duplicated data and press the OK button.

Illustration 3: Select duplicated columns.

5) Excell removes duplicated rows and raises a message.

Illustration 4: Duplicated removal results.

Data Validation

6) Go to the next free row.

7) Select D35 cell.

8) Select the Data Validation command from the Data Tools Group in the Data in the Options Tape.

Illustration 5: Data Validation tool.

9) Configure the allowed data for D35 cell: a number between 100 and 200.

Illustration 6: Allowed data configuration.

10) Create an input message for D35 cell.

Illustration 7: Input message.

11) Create an error message for D35 Cell.

Illustration 8: Error Message.

12) Notice the warning as a ToolTip in the cell.

Illustration 9: Warning message.

13) If you try to insert a 250 in D35 cell, the error message will be raised.

Illustration 10: Error Message.  Click here to view larger image.

14) Drag the cell D35 from D35 to D40. The validation will be copied.

Illustration 11: Copying a validation.

Creating Pivot Charts

15) Select the Pivot Chart command from the Tables Group in the Insert in the Options Tape.

Illustration 12: Pivot chart creation.

16) Excel recognizes the table data as the source. Press the OK button.

Illustration 13: Select data range.  Click here to view larger image.

17) A blank Pivot Chart template will be displayed.

Illustration 14: Blank chart template.

18) Select the fields to show as data series in the chart.

Illustration 15: Pivot Table field List.

19) Configure the filtering options.

Illustration 16: Format chart options.

20) Quit the Pivot char filter pane and see the results.

Illustration 17: Filtered Table.  Click here to view larger image.

21) Change the sumarize operation to average and see the results.

Illustration 18: Change the summarize operator.

Dynamic Formulas

22) Select F17 cell.

23) Select the Logical command from the Function Library Group in the Formulas menu in the Options Tape.

Illustration 19: Change the summarize operator.

24) Create a formula to show the comparison results between space used by table A and table B.

25) Click on Logical_Test text box in the Formula editor. Then click in cell B2. Write a > sign and then click in cell B3. Click in Value_if_true text box.

Illustration 20: Create an If function.

26) Write “Table A has more space than Table” B in Value_if_true text box.

27) Write “Table A has less space than Table B” in Value_if_false text box. Press the Ok button and see the results.

Illustration 21: Function Results.

Source: MS TIPS AND TRICKS

9/3/2007 9:33 AM

soumya

windowsxperience.wordpress.com/
Joined: 8/8/2006

Posts: 5438

Re: Excel 2007 Tips & Tricks Modified By soumya on 9/3/2007 9:37:11 AM

Graphs offer a visual representation of the data. Instead of having to analyze columns of values of spreadsheet, it can interpret the meaning of the data of a single look. A graph transmits the messages quickly. With a graph, the data of spreadsheet can be transformed to show comparisons, patterns and tendencies.

Creating a basic Chart

1) Open the Table Usage.xlsx Sample.

Illustration 1: Table usage sample excel file.

2) Select the data that wish to represent, including the titles of the columns.

3) Next, it clicks in the Insert Group and, in the sub group Charts, click in the Column button.

Illustration 2: Creating Graphs. Click here to view larger image.

4) The chart will be created and placed near the data table.

Illustration 3: New chart created. Click here to view larger image.

5) After creating a graph, you can add new elements easily. For example, you can add titles to it to offer more information or change the element’s position.

6) When creating a graph, in the Options Tape appears the Graph’s Tools, which include Design, Distribution and Format groups. In these groups youwill find the commands necessary to work with the graphs.

Illustration 4: Chart Tools.

7) When the graph is finished, click outside it. The Graph’s Tools will disappear. Click within the graph so that they will be displayed again.

8) To change the graph title, double click on the Space Used (KB) Title, then write the new title.

Illustration 5: Changing Title Text.

9) To format the graph title, click on the Space Used (KB) Title, then select the new font format from the Font Group.

Illustration 6: Changing Title Font style. Click here to view larger image.

10) To change the Chart type select the chart area, then rigt click and select the Change Chart Type menu item.

Illustration 7: Changing the chart type.

11) To change the Chart type select the chart area, then rigt click and select the Change Chart Type menu item.

12) Select the Line chart type and press the OK button.

Illustration 8: Selecting the chart type. Click here to view larger image.

13) The new chart type will be applied.

Illustration 9: Chart type changed.

14) To format the data series, select the chart data series, right click and select the Format Data Series menu.

Illustration 10: Formatting data series.

15) Select the desired options as follows:

Illustration 11: Marker.

Illustration 12: Marker Fill.

Illustration 13: Line Color.

Illustration 14: Line Style.

16) Select the vertical value axis, then right click on the Format Axis … menu.

Illustration 15: Formatting Axis.

17) Select the options to configure the numbers format.

Illustration 16: Configure the numbers format.

18) Select the options to configure the numbers format. Press the Close button to apply yhe format.

19) Select the Data Labels, then right click on the Format Axis … menu. Also, you could use the quick format group.

Illustration 17: Formatting data labels.

20) Select the Data Labels, then right click on the Format Axis … menu. Also, you could use the quick format group.

21) Format the X Axis as showed in step 15. Select the bests options that fits to your needs.

22) To quickly change the chart style, select the chart and then select the More button from the Chart Layout group.

Illustration 18: Changing chart style.

23) Select the desired layout. The new style will be applied.

24) To format the legend, right click on it and select the Format Legend… menu item.

Illustration 19: Changing the legend format.

25) Select the desired option and press the Close button.

9/3/2007 9:35 AM

soumya

windowsxperience.wordpress.com/
Joined: 8/8/2006

Posts: 5438

Re: Excel 2007 Tips & Tricks Modified By soumya on 9/3/2007 9:37:53 AM

In this tutorial, you will learn how to use the formulas.

Using Formulas

1) Open Sample.xlsx book.

Illustration 1: Sample book. Click here to view larger image.

2) Select cell H1. Write Table Name as new column name.

3) Select cell H2.

4) Select the Insert Function command from the Functions library group in the Formulas Tab.

Illustration 2: Function Library.

5) Select the RIGHT function from the function list in Text Category. Press the Ok Button.

Illustration 3: Function categories.

6) Select the cell B2, which contains the text where you want to extract only the table name by discarding the dbo. Text.

Illustration 4: Text to evaluate in cell B2.

7) You wants to extract all text by discarding the four initial characters (dbo.). As the formula requires the characters quantity to extract, you need calculate the string length and then discard 4 characters.

8) The function LEN(B2) will return the string length.

9) LEN(B2) – 4 will return the total string length discarding 4 characters.

10) Complete the RIGHT function as follows.

Illustration 5: RIGHT function with a field Num_chars containing another formula.

11) Press the OK button and see the results.

Illustration 6: RIGHT function results. Click here to view larger image.

12) Select cell I1. Write Avg Space as new column name.

13) Select cell I2.

14) Select the Insert Function command from the Functions library group in the Formulas Tab.

Illustration 7: Function Library.

15) Select the AVERAGE function from the function list in Statistical Category. Press the Ok Button.

Illustration 8: Function categories.

16) Select the range where the arithmetic mean will be calculated. Select the range D2:G2.

Illustration 9: Function categories.

17) In the bottom left corner you will set the result. Press the OK Button.

18) Select cell J1. Write Must Be Reserverd as new column name.

19) Select cell J2.

20) Select the Insert Function command from the Functions library group in the Formulas Tab.

Illustration 10: Function Library.

21) Select the IF function from the function list in Logical Category. Press the Ok Button.

Illustration 11: Function categories.

22) Configure the logical test.

Is Avg Space * 1.5 < (Data + Indexes) >>> (Data + Indexes).

Else >>> Avg Space * 1.5.

Illustration 12: Logical test.

23) In the bottom left corner you will set the result. Press the OK Button.

24) Select cell K1. Write vlOOK as new column name.

25) Select cell K2.

26) Select the Insert Function command from the Functions library group in the Formulas Tab.

Illustration 13: Function Library.

27) Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

28) Select the TRANSPOSE function from the function list in Lookup & Reference Category. Press the Ok Button.

Illustration 14: Function categories.

29) Configure the range to transpose.

Illustration 15: Function range.

30) Configure the range to transpose.

Illustration 16: Transpose function results. Click here to view larger image.

9/3/2007 1:22 PM

Vishal Gupta

www.AskVG.com
Joined: 8/9/2006

Posts: 6662

Re: Excel 2007 Tips & Tricks

wow. Nice article buddy. thnx for it.

9/3/2007 5:48 PM

Piyush Gupta

Joined: 5/27/2007

Posts: 1834

Re: Excel 2007 Tips & Tricks

Great collection buddy awsome work

Read Forum Guidelines before posting

9/4/2007 12:22 PM

soumya

windowsxperience.wordpress.com/
Joined: 8/8/2006

Posts: 5438

Re: Excel 2007 Tips & Tricks

Microsoft Office Excel 2007 is an effective tool that can use to create and to apply format to spreadsheets, and to analyze and to share information to make decisions better founded.

Office Excel 2007 takes advantage of its new user interface oriented to the results to facilitate the access to effective productivity tools. Also it offers more space to work and a faster operation.

Opening Excel

1) Click on the Start button. Then select Programs >> Microsoft Office >> Microsoft Office Excel 2007 menu item.

2) A new blank workbook, Book1, will be displayed.

Illustration 1: Excel empty workbook. Click here to view larger image.

3) Upper the grid area, you will see the columns, named with letters from A.

4) A t the left area, you will see the rows numbers. Then, cells are referenced by their column name and row number. As you can see in the above image, the cursor is positioned at cell D8.

5) The Options Tape extends throughout the superior part of Excel and their related commands are organized in groups. Each group is related to a specific work that the people can make in Excel.

Illustration 2: Options Tape and Groups. Click here to view larger image.

6) In order to see the different commands in each group, you must click in groups located in the superior part of the tape of options. The card Home, that is the first card beginning by the left, contains the habitual commands that people use more frequently.

7) The commands are organized in small related groups. For example, the commands to edit cells are in the group Font, and the commands to work with cells are in the group Cells.

Illustration 3: Commands related to a group.

8) The first book opened is denominated Book1. This title appears in the bar at upper side of the window until the book is saved with a new name. Each new book has three sheets, like the pages of a document. Within the sheets you can specify data. Sometimes the sheets receive the name of spreadsheets. It is recommended to change the sheet’s name to more easily identify the information contained in each one.

Illustration 4: Book sheets.

Creating a new WorkBook

9) To create a new book, click on the Office button in the upper left side, then select the New menu item. Select Blank WorkBook from the window.

Illustration 5: Create a new workbook. Click here to view larger image.

10) Columns extend from top to bottom in the spreadsheet, that is to say, vertically. Rows extend of left to right in the spreadsheet, that is to say, horizontally. Cells are the space where columns and rows are intersected. Each book has 16.384 columns and 1.048.576 rows. When you open a new book, the first cell is the active cell.

Working with cells

11) Cells are the space where data in the spreadsheet are specified. When you open a new book, the first cell that you see in the left superior corner of the spreadsheet appears in black, indicating that in it will be the showed data that you specify.

12) You can specify data in the place that you wish by clicking in the corresponding cell of the spreadsheet to select it.

Illustration 6: Entering some text in a cell.

13) You can select an entire column by clicking in its name.

Illustration 7: Selecting a column.

14) You can select an entire Row by clicking in its number.

Illustration 8: Selecting a row.

Keyboard abbreviated methods

15) The Office button has replaced the older version’s File menu. Here you will find the basic commands as Save, Save As …, etc.

Illustration 9: Office button.

16) The Quick Access toolbar is located at the top edge on the screen (upper the Options Tape) and contains the most used commands.

Illustration 10: Quick Access toolbar.

17) There are two ways to use the keyboard: to gain access to the groups and commands in the screen or use a couple of keys to execute some commands not related to the Options Tape.

18) The access keys provide access to the tape of options. They relate directly to groups, the commands and other elements that are seen in the screen. The access keys are used by pressing ALT key at the same time with another key or a sequence of other keys.

19) A key’s combination executes specific commands. These key’s combination are no related to the Options Tape.

20) As soon as you press ALT key, you will see small labels or identifiers that will appear to show the suggestions of keys of all groups.

21) After pressing a key to activate a concrete group, it will appear the identifiers that show the suggestions of keys for the commands of that group.

Illustration 11: See the Options Tape when ALT key is pressed. Click here to view larger image.

22) The Tab key will allow you to navigate the Options Tape once ALT + Key was pressed.

Illustration 12: Navigate the Options Tape with the TAB key.

23) Press UPPER+TAB in order to cross the commands in opposite direction. Press ENTER to select the desired command.

24) You can go to the different areas of the program window by pressing F6.

25) The space bar activates and deactivates options checks.

26) UPPER + F10 key activate the context’s menus.

27) ESC close context’s menus.

9/4/2007 12:23 PM

soumya

windowsxperience.wordpress.com/
Joined: 8/8/2006

Posts: 5438

Re: Excel 2007 Tips & Tricks

In this tutorial, you will learn how to configure Excel’s general options.

Editing Excel Options

1) Open Excel Application.

2) Create a new blank workbook.

3) Select sheet 1, cell A1.

4) Write “Index Cluster 1 - Idxa“.

5) Drag the cell from A1 to A9.

6) See the results.

Illustration 1: Dragged data list.

7) Click on the Office Button similar to the one shown in the below image.

8) Click in the Excel Options button.

9) Select the "Popular" tab as shown below.

10) Select the "Edit Custom Lists..." button as shown below.

11) Create a new list. Press the Add button.

Illustration 2: Customs lists.

12) Click on List Entries and tipe: Custom1, Custom 2, Custom 3 and press the Add button.

Illustration 3: Customs lists.

13) Select an empty cell and write Custom 1.

14) Drag the cell and see the results.

Illustration 4: The new list is applied when you drag the cell.

15) Another way to generate customs lists is reading it from a sheet. Create a new list.

16) Click in Import list from cells text box.

Illustration 5: Select the range.

17) Select a range from a sheet and press the Import button.

Illustration 6: Select the range. Click here to view larger image.

18) The data will be imported as showed. Press the Ok Button.

Illustration 7 Select the range.

19) Select the SampleMacroForPivotUpdate macro and press the Edit button.

20) Write “Index Cluster 1 – Idxa” in an empty cell.

21) Drag the cell and see the results.

22) Select the "Formula" button as shown below.

23) Be sure that automatic calculation is on. Formulas will remain without recalculation is this setting is configured in manual calculation.

24) To customize the Quick Access Toolbar, use the "Customize" tab as shown below. Here you could select the commands to show to have quick access to the most used commands.

25) To configure add-ins (additional non standard functions that expands Excel), select the "Add-Ins" tab as shown below, then select Excel Add-ins from the list box Manage and press the Go … button as shown below.

Illustration 8 Select the range.

26) Select the desired add-ins and press the Ok button.

Illustration 9: Add-ins selection.

9/4/2007 12:27 PM

soumya

windowsxperience.wordpress.com/
Joined: 8/8/2006

Posts: 5438

Re: Excel 2007 Tips & Tricks

In this tutorial, you will apply formatting options to cells and prepare the sheet for printing.

Formatting cells

1) Open the Table Usage 2007.xlsx Sample.

Illustration 1: Table usage sample excel file. Click here to view larger image.

2) Select the titles rows. Use the Horizontal Alignment group to center the titles.

Illustration 2: Horizontal center alignement.

3) Select the titles rows. Use the Verical Alignment group to center the titles.

Illustration 3: Select the range.

4) Select all cells containing numbers. Right click and select the Format cell menu item.

Illustration 4: Blank Pivot table layout.

5) Configure all formatting options and press the Ok button.

Illustration 5: Formatting options.

6) Continue formatting the cells until you’re satisfied.

Illustration 6: Drag and drop fields. Click here to view larger image.

7) Another way to quickly format a data table is to apply a table format. Select the Format as Table command from the Styles Group .

Illustration 7: Formatting as table.

8) A style’s list will be displayed. Pick up the desired layout.

Illustration 8: Table Layouts.

9) A dialog box will appears with the table range selected. Press the Ok Button.

Illustration 9: Table Range.

10) As you cann see, the new layout is applied. Also, a Data filter appears in each column title botton corner.

Illustration 10: Automatic format. Click here to view larger image.

Formatting cells

11) You could set up specials conditions for automatic format. Select a cell and then, select the Conditional Formatting command from the Styles Group.

Illustration 11: Conditional Formatting.

12) Select the Highlight Cells Rules, then Greater than menu item.

Illustration 12: Formatting rules.

13) Select the format condition and format style and press the OK button.

Illustration 13: A new worksheet.

14) The cell will be formated when the condition is true.

Illustration 14: Formatted cell. Click here to view larger image.

15) Select the Top / Bottom Rules, then Top 10 items menu item.

Illustration 15: Formatting rules.

16) Select the TOP condition and format style and press the OK button.

Illustration 16: Changing the legend format.

17) The cell will be formated when the condition is true.

Illustration 17: Changing the cell format. Click here to view larger image.

18) You can copy a formatting condition. Select the cell that contains the desired conditional formation and select the Format Painter command from the clipboard group.

Illustration 18: Selecting the cell format.

19) The cursor show a paintbrush. Paint all target cells. These cells will take the pasted format.

Illustration 19: Copying Format.

Illustration 20: Format applied.

Formatting Sheets for printing

20) To see a Print Preview, click on the Office button. Then select Print and click in the Preview Button.

Illustration 21: Format applied.

21) To see a Print Preview, click on the Office button. Then select Print and click in the Preview Button.

Illustration 22: Preview window. Click here to view larger image.

22) Select the Page Setup command from the Print Group.

Illustration 23: Page setup.

23) Configure Page Margins. Select the Center Page options to center the printable area in the sheet.

Illustration 24: Margins setup.

24) Configure Header / Footer. Select the Custom Header button to open the header window. Write the headers. Select font and built-in formulas to show date and time, page numbers, etc.

Illustration 25: Header setup. Click here to view larger image.

25) Configure Header / Footer. Select the Custom Footer button to open the footer window. Write the footers. Select font and built-in formulas to show file path, sheet name, etc.

Illustration 25: Footer setup. Click here to view larger image.

26) See the results at preview window.

9/4/2007 2:39 PM

Vishal Gupta

www.AskVG.com
Joined: 8/9/2006

Posts: 6662

Re: Excel 2007 Tips & Tricks

^^ gr8 buddy. u r rocking.

9/4/2007 3:10 PM

Piyush Gupta

Joined: 5/27/2007

Posts: 1834

Re: Excel 2007 Tips & Tricks

Awsome buddy

Read Forum Guidelines before posting

9/5/2007 11:26 AM

soumya

windowsxperience.wordpress.com/
Joined: 8/8/2006

Posts: 5438

Re: Excel 2007 Tips & Tricks

In this tutorial, you will learn how to create and use Excel’s macros.

Creating Macros

1) Open the Sample.xlsx Sample.

Illustration 1: Sample book.

2) Select the Macros command from the Macros Group in the View tab in the Options Tape.

Illustration 2: Macros.

3) Set the macro name, shortcut key, store location and description.

Illustration 3: set up macro properties.

4) Press the OK button to start recording actions.

5) Select the Pivot table Report. Right click and select the Refresh menu item.

Illustration 4: Refresh Menu.

6) Stop recording.

7) Go to the table and change some values in the Space column.

8) Select the View Macros command from the Macros Group in the View tab in the Options Tape.

Illustration 5: Duplicated removal results.

9) Select the SampleMacroForPivotUpdate macro and press the Run button.

Illustration 6: Select the macro to run.

10) The macro will update the Pivot Table data.

Editing Macros

11) Select the View Macros command from the Macros Group in the View tab in the Options Tape.

12) Select the SampleMacroForPivotUpdate macro and press the Edit button.

13) The Visual Basic for Applications code editor will be opened. The macro code will be displayed.

Illustration 7: VBA Editor. Click here to view larger image.

14) Delete the last two lines.

Illustration 8: Error Message. Click here to view larger image.

15) Write an Information message by adding a new line.

MsgBox "Pivot Table data has been refreshed", vbExclamation + vbInformation, "Macro Finished".

Illustration 9: Warning message. Click here to view larger image.

16) Close the editor and run the macro again.

17) See the results.

Illustration 10: Information Message.

18) Select the View Macros command from the Macros Group in the View tab in the Options Tape.

19) Select the SampleMacroForPivotUpdate macro and press the Step Into button.

20) The Visual Basic for Applications code editor will be opened. The macro code will be displayed with a yellow bacground over the macro name.

Illustration 11: step into code. Click here to view larger image.

21) Press the F8 key. The cursor will advance to the next executable code line.

22) Press F8 key twice again.

23) Run the Refresh sentence.

Illustration 12: Execute the line with F8 key. Click here to view larger image.

24) Debug each line to see the final results.

25) Close the VBA code editor.

9/5/2007 11:28 AM

soumya

windowsxperience.wordpress.com/
Joined: 8/8/2006

Posts: 5438

Re: Excel 2007 Tips & Tricks

With the Pivot data you can see the same information in differents layouts with a few clicks.

Illustration 1: Sample Pivot Table Report.

Dynamic Tables (or Pivot tables) are summarized rows whose source data belongs to a sheet.

Creating a Pivot table

1) When you create a dynamic table, each column of its source data becomes a field that can be used in the pivot table. The fields summarize rows of information of the data source.

2) The name of the fields for the pivot table comes from the titles of the columns of their data source. Verify that you have names for each column of the first row of the spreadsheet of the data source.

3) The remaining rows that appear under the headers would have to contain similar elements of the same column in the data source.

4) it must not have empty columns in the data that are using for the report of the dynamic table.

5) Open the Table Usage.xlsx Sample.

Illustration 2: Table usage sample excel file. Click here to view larger image.

6) Select the data that wish to represent, including the titles of the columns.

7) Select the Insert menu and the Pivot Table command from the Tables Group.

Illustration 3: Creating Graphs.

8) Notice that the range will be automatically displayed.

Illustration 4: Select the range. Click here to view larger image.

9) Now select the New Worksheet option and press the Ok button.

10) A new worksheet with the Pivot table blank design will appear.

Illustration 5: Blank Pivot table layout.

11) At the right side, you will see the Pivot Table Fields List.

Illustration 6: Pivot table Fields list.

12) Click on each field that you wants to add to the Pivot Table design or drag and drop the fields to the headers.

Illustration 7: Drag and drop fields.

13) If you want to see only Table A data, you could set up the filters.

Illustration 8: Filtering data.

14) To change the Chart type select the chart area, then rigt click and select the Change Chart Type menu item.

Illustration 9: Data filtered.

15) You could change the sumarization’s formulas by right click on the header to change and selecting the Summarize Data By … menu item.

Illustration 10: Chart type changed.

16) As you can see, now you are viewing Average and Max operations.

Illustration 11: Changing formulas.

17) You could see the rows composition for that subtotal by right click and selecting the Show Details menu item.

Illustration 12: Showing details.

18) A new worksheet with the rows that compose the sub total will be created.

Illustration 13: A new worksheet.

19) You could set up the Pivot table options by clicking on the Pivot Table and selecting the Pivot Table Options menu item.

Illustration 14: A new worksheet.

20) Give a name to the Pivot table and configure all presentation format options.

Illustration 15: Pivot table Options.

21) You could format the numbers to make it more clear to view. Right click on any cell and select the Number Format menu item.

Illustration 16: Format number option.

22) Format the number as follows and press the Ok Button.

Illustration 17: Changing the legend format.

23) You could format the cells to make it more clear to view. Right click on any cell and select the Format Cells menu item.

Illustration 18: Changing the cell format.

24) Select the desired options and press the Ok Button.

Illustration 19: Selecting the cell format.

25) If you change the source data sheet, refresh the Pivot table by right click and select the Refresh menu item.

Illustration 20: Refreshing data.

26) You could sort your data by right click and select the Sort menu item.

9/8/2007 12:16 AM

soumya

windowsxperience.wordpress.com/
Joined: 8/8/2006

Posts: 5438

Re: Excel 2007 Tips & Tricks Modified By soumya on 9/8/2007 12:19:05 AM

In this tutorial, you will apply connect with another data source to obtain data.

Making a connection

1) Open the http://www.mstipsandtricks.com/tips-and-tricks/ms-office-tips-and-tricks/ms-excel/working-with-data/Disk%20Usage.xlsx SAMPLE

2) Create a new work sheet by clicking the tab as shown below.

Illustration 1: New Work sheet Tab.

3) Select the A2 cell.

4) Select the Connections command from the Connections Group in the Data in the Options Tape.

Illustration 2: Select Connections.

5) Click on the Add button.

Illustration 3: Add new connection window.

6) Click in the Browse for more button.

Illustration 4: Browse connections.

7) You will connect to an SQL Database. As the connection does not exists, click in the New Source Button.

Illustration 5: Create new source. Click here to view larger image.

8) Select the SQL Server Database and press the Next button.

Illustration 6: Select the source.

9) Provide the Server Name and user credentials. Press the Next button.

Illustration 7: Configure the connection.

10) Select Database name and table to connect. Press the Next button.

Illustration 8: Table Range.

11) Provide a connection name and description for save it for later use. Press the Finish button.

Illustration 9: Automatic format.

12) Workbook Connections list will be refreshed. Select the new connection and press the Close button.

Illustration 10: Refreshed list.

Getting Data

13) Select the Existing Connections command from the Get External Data Group.

Illustration 11: Select the new connection.

14) Select the recently created SQL Repo connection and click in the Open button.

Illustration 12: New connection available for selection.

15) Select the import method prefered and the destination cell. Press the OK button.

Illustration 13: Setting data imported format.

16) Provide user credentials for SQL Server. Press the OK button.

Illustration 14: User identification.

17) See the results.

Illustration 15: Imported Data.

Using Filters

18) After data import, you will see an arrow at each column title bottom corner. These arrorws represent filters for data. Click in the arrow in column name.

Illustration 16: Changing the cell format.

19) Click on Select All, the click and select Collect Data Option. Press the OK button.

Illustration 17: Selecting the cell format.

20) The filter will hide all rows that do not match with the selected Collect Data text in column Name.

Illustration 18: Filtered Table.

21) To see all rows, open the filter in column Name and Click on Select All option. Press the OK button.

Illustration 19: Restoring view.

22) To exit from the Filter option, de select the Filter option from the Sort & Filter command in the Editing Group.

Illustration 20: De selecting Filtering option.

23) You could filter by other conditions.

Illustration 21: Filters. Click here to view larger image.

24) Select the A2 Cell. Select the Font Color as red.

Illustration 22: Change cell’s font color.

25) Select the Filter by Selected cell’s Font color option and see the results.

Illustration 23: Filtering by cell’s font color.

9/8/2007 12:22 AM

soumya

windowsxperience.wordpress.com/
Joined: 8/8/2006

Posts: 5438

Re: Excel 2007 Tips & Tricks

Excel is an effective tool to work with numbers and to do mathematical operations. Sometimes, for do these operations are necessary to insert formulas.

Inserting a formula

1) Open the Budget.xlsx Sample.

Illustration 1: Budget sample excel file. Click here to view larger image.

2) You will sum cells E4 and E5 and put the results in the cell E6.

3) To write a formula (or some text), click on the target cell. When you write, the results will be displayed in the formula bar.

Illustration 2: Writing formulas.

4) Another way to write formulas is clicking in the target cell, write an equal (=) sign, then click the first operator, click in the plus (+) sign, click in the second operator and press ENTER to accept the formula and display results. The formulas of Excel always begin by an equal sign.

5) If later you wish to know how excel has obtained the results, the formula will be visible in the formula bar.

Illustration 3: Viewing formulas.

6) Bellow is the mathematical operations list:

Mathematical Operators

Sum (+)

=10+5

Subtraction (-)

=10-5

Multiply (*)

=10*5

Divide(/)

=10/5

Within each formula it can use more of a mathematical operator.

7) To sum cells range, yo need to select the target cell and press the sum button.

Illustration 4: The auto sum button.

Illustration 5: The auto sum operator takes the nearest numeric cell range.

8) To copy a formula cell Drag the black cross from the cell that contains the formula until the cell where you wish to copy it and it and release the mouse button.

Working with cell’s references

9) The cell’s references can indicate independent cells or ranks of cells in columns and rows.

Cell’s reference

Refers to

A10	Cell in column A and row 10

A10;A20

Cell A10 and cell A20

A10:A20

Cell’s range from cells in column A from row 10 to 20

B15:E15

Cell’s range from cells in row 15 from columns B to E

A10:E20

Cell’s range from column A row 10 to column E row 20



Illustration 6: Range A10:A20.


Illustration 7: Range A10;A20.


Illustration 8: Range B15:E15.


Illustration 9: Range A10:E20.

10) Another way to specify cell’s references. In the C9 cell, writes the equal sign, writes SUM and write an opening parenthesis.

11) Click in the C4 cell and, next, write a semicolon in the C9 cell.

12) Click in the C6 cell and, next, writes a closing parenthesis in the C9 cell.


Illustration 10: Writing formulas.

13) Press ENTER to show the formula results.

14) You can directly write cell’s references in the cells, or specify clicking them in the corresponding cells, which avoids write errors.

15) A relative cell’s reference for a formula change automatically when the formula is copied in others columns or rows.

16) An absolute cell’s reference of cells is fixed. The absolute references do not change when copying a formula of a cell in another one.

17) A mixed cell’s reference has either an absolute column and a relative row, or an absolute row and a relative column.


Illustration 11: Relative and absolutes references.

18) The references of relative cells change from a row to another one.

19) The absolute reference always makes reference to the E29 cell.

20) The E29 cell contains the value for the discount of the 7 percent.

21) Use references of absolute cells to make reference to cells that that does not wish they change when copying the formula.

Working with functions

22) Functions names allows to express writing for long formulas.

Cell’s reference

Refers to

AVERAGE

The average

MAX	The most great number

MIN	The minor number



23) These predefined formulas simplify the process of calculation’s introduction. The functions allow the easy and quickly formulas creation, that can be complicated for the user.

24) Select the function icon from the formula bar.


Illustration 12: Select the function icon.

25) Select the *AVERAGE* function from the *Insert Function* window.


Illustration 13: Insert Function window. Click here to view larger image.

26) Press the OK button. Then select the range cells that compose the average by clicking in the first cell and dragging to the last cell in the range.


Illustration 14: Select the range. Click here to view larger image.

27) Press the OK button to see the results.


Illustration 15: Showing the results.

28) To see the formulas, click on the Formula Auditing group in the options Tape, Then select the Show Formulas command.


Illustration 16: Showing formulas in the sheet.


Illustration 17: Formula view.
This is the end of another tutorial. I hope you guys liked it and that I have been of some use