Excel 2007 Tips & Tricks - Microsoft Excel - Microsoft Windows Community

Microsoft Windows Forums Microsoft Offic... Microsoft Excel Excel 2007 Tips & Tricks

9/3/2007 9:31 AM

	soumya
2849 posts
microsoftblog4u.blogspot.com/
Forum Guru

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
2849 posts
microsoftblog4u.blogspot.com/
Forum Guru

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
2849 posts
microsoftblog4u.blogspot.com/
Forum Guru

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
6366 posts
www.AskVG.com
Ultimate Member

Re: Excel 2007 Tips & Tricks

wow. Nice article buddy. thnx for it.

How to Use Smiley Code in Forum?
Promote MeraWindows at Your Blog / Site
Read Forum Guidelines

9/3/2007 5:48 PM

	Piyush Gupta
1834 posts
www.piyushworld.com
MW Addict

Re: Excel 2007 Tips & Tricks

Great collection buddy awsome work

Read Forum Guidelines before posting

Page 1 of 4

Microsoft Windows Forums

Microsoft Offic...

Microsoft Excel

Excel 2007 Tips & Tricks

Get Your Own E-Mail Account @MeraWindows.com

New Page 1 New Page 1

Show your cool quotient with @merawindows.com email account

Check e-mail