Category Archives: Excel 2010 English

How To Delete All Objects At Once In A Spreadsheet

Assuming that we have charts and objects in our spreadsheet and we come to the point that we are not satisfied with the appearance of them and we want to delete them all at once. What do we do?

Instead of selecting them one by one (procedure which will take a long time) and then pressing the delete button every time, we can follow the next tip.

On the Home tab of the Excel window and in the area of the ribbon named Editing, we select the Find & Select button. From the drop down menu that appears we select the Go To Special… command as we can see in the image below.

01

The Go To Special window appears, where we activate the button next to the command Objects as we can see in the image below and the press the OK button.

03

Once we press the OK button, we will notice that all the objects in our spreadsheet are now selected.

Now all we have to do is to press the delete button (seeing that, that is what we wanted) in order for all the objects to be deleted.

04

Below you can check out the video describing on how to delete all the objects in our spreadsheet very quickly.

Advertisements

Trace Precedents and Dependents in Excel

Excel offers us some useful tools for auditing a formula, in order to understand what we have done wrong in the formula, by tracking down the relationships between the cells of the formula in a spreadsheet. By tracking the cells we will be able to locate the Precedents cells and the Dependents cells.

What are Precedents and Dependents though?

Precedents are cells or ranges of cells that affect the active cells value.

Dependents are cells or ranges of cells that are affective by the active cell.

In formulas that we create in Excel, they may contain precedents, and maybe they are dependents on other formulas.

In order to locate the Precedents and Dependents in Excel we must do the following:

First of all we must select the cell that we want to trace the Precedents or the Dependents. Then we select the Formulas tab and then from the area of the ribbon named Formula Auditing we select either Trace Precedents or Trace Dependents as we can see in the image below.

01

In the image below I have selected the J10 cell (which contains a formula) and then clicked on the Trace Precedents button once. Straight away the blue arrows appear showing what cells affect the value of the currently selected cell.

01a

In the image below I have clicked on the Trace Precedents button once more, and more blue arrows appear showing us which cells affect the value of the primary precedents cells.

02

In the image below I have selected the cells C9, D9, E9, F9, G9 one by one and each time I selected one cell I also activated the Trace Dependents button. Once finished the blue arrows indicate what cells are affected by the value of the currently selected cells.

03

If we double click on a blue arrow, that will activate the cell on one end of the line, and if we double click it again it will activate the cell on the other end.

Once we have finished with Tracing the Precedents and Dependents we can select the command Remove Arrows and from the drop down menu we can select either to:

  • Remove Arrows
  • Remove Precedent Arrows
  • Remove Dependent Arrows

04

By using the Trace Precedents and Trace Dependents commands we will be able to understand our formula much better and if anything is wrong with it we will be able to locate the error also.

Below you can check out the video in order to understand how to use the Trace Precedents and Trace Dependents functions in Excel.

Remove Duplicates in Excel 2010

du·pli·cate

adj.

  1. copied exactly from an original
  2. identical
  3. existing as a pair or in pairs; twofold

Many times when Excel is used to manage lists of data, duplicates may exist in the lists of data and needed to be identified and deleted. How can we identify the duplicates in our Excel spreadsheet?

In the spreadsheet below we want to find the duplicates for the Product column, (imagine the data being many more than this actual example). Instead of going through our data one by one in order to locate the duplicate we can do the following:

01

First of all, we must select a cell inside the area of the data and then activate the Data tab and from the area of the ribbon named Data Tools we select the command Remove Duplicates as we can see in the image below.

02

Once selected the Remove Duplicates window appears as we can see in the image below. By default the My Data Has Headers command is activated, and all the headings of our spreadsheet are selected.

02a

If we do not have headers in our spreadsheet we need to de-activate the My Data Has Headers command, and the Remove Duplicates window will appear as the image below.

03a

If we want to uncheck all the headings we just press the Unselect All button. Then we click on the check box at the left of the column name that we want to check for duplicates. We then press the OK button in order for Excel to search and delete the duplicates.

03

The information dialog box appears notifying us that one duplicate value was found and removed, and that six unique values remain as we can see in the image below.

04

If we have selected a cell outside of the data area and try to use the Remove Duplicates function the following warning dialog box will appear notifying us that the command could not be completed because of the range specified.

05

If we don’t want to remove the duplicates in our spreadsheet, but to highlight them in order to be examined we can use the Conditional Formatting command.

In order to use it, we select the Home tab and from the area of the ribbon named Styles we select the command Conditional Formatting as we can see below.

06

Once the drop down menu appears we select the Highlight Cells Rules and from the right of the menu we navigate to the bottom of the secondary menu and select the Duplicate Values command.

The Duplicate Values dialog box appears where we can select what sort of formatting the Duplicates or Unique values will have as we can see in the image below.

08

Straight away our duplicate or unique values will be highlighted according to the format that we had chosen.

Below you can check out the video describing how to use the Remove Duplicates function in Excel 2010.

Check And Evaluate Formulas In Excel 2010

We have just finished a formula in Excel (which we are proud of) but we want to make sure that what we did is correct. What do we do?

We can use Excel’s Evaluate Formula function. It helps us go through the steps that we used when we were creating the formula. Very useful function especially for long formulas and ones that have precedents, because it makes it very easy to see how Excel has calculated this formula.

First of all we must select the cell that contains the formula and then select the Functions tab and then from the area of the ribbon named Formula Auditing we select the command Evaluate Formula as we can see in the image below.

01

Once selected the Evaluate Formula dialog box appears as we can see in the image below. At the top left corner of the window and under Reference, we see the cells reference which is IF!$I$4 (the actual name of the sheet and the actual cell).

In the Evaluation area of the Evaluate Formula window we see our formula. In this example the formula is =IF(Criteria!A2>=10000;“Bronze”; “Gold”).

02

We notice that a part of the formula is underlined (Criteria!A2), so if we click the Evaluate button, we will see the actual value that the specific cell contains, as we can see in the following image.

03

Now the part 6300>=10000 is underlined. If we click the Evaluate button, we will see the result of the first part of the formula. Seeing that it was an If function and the criteria 6300>=10000 is not True, the result that appears is False as we can see below.

04

By pressing once more the Evaluate button the actual result of the formula appears as we can see. The Evaluate button changes to Restart in order to start evaluating the formula again.

05

When we are evaluating a formula, we notice the Step In and Step Out buttons that appear on the Evaluate Formula dialog box. These buttons are enabled during the steps that have got to do with precedents as we can see in the image below.

06

Here we see the actual sheet and cells reference Criteria!$A$2 that we have stepped in, and at the right we see the actual formula that the specific formula contains. Once we have checked it we can press the Step Out button in order to continue Evaluating the formula.

Below you can check out the video which describes how to use the Evaluate Formula in Excel 2010.

Transpose Function For Linked Data

As mentioned in my previous post, when we use the Paste Transpose command, the data between the two tables are not linked to each other. Below I will describe how we can Transpose data and with any data that we change in our first table, instantly the data will be changed also in the transposed version of the table.

In order to accomplish this, we count the Rows and Columns of the table that we want to Transpose. For example in the image below the area of the table is A2:E9, which means it consists of 5 columns and 8 rows.

01. Select Table

Now we must select a blank region of our worksheet with the dimensions transposed. Meaning that we have to select and area that has 8 columns and 5 rows as we can see in the image below.

02. Select Empty Cells

Now in the upper left center of the selected area we must type in the formula =TRANSPOSE(A2:E9), where A2:E9 is the address of our actual table.

03. Type In Formula

Once we have typed in the formula, we must press the combination of keystrokes Ctrl+Shift+Enter (which is known as an Array Formula).

Our data has been Transposed, and if we notice in the formula bar, we will see our formula with brackets around it as shown in the image below indicating that it is an Array formula.

05. Formula Bar

In the image below we can see our Transposed data, and any changes that we will make in the original table will also appear in the Transposed version. We cannot make any changes in the transposed version of the table.

04. CtrlAltDelete

You can also check out the video below, describing the steps in order to use the Transpose Function in Excel for linked data:

How To Use Paste Transpose

Sometimes we want to convert the rows to columns and the columns to rows in an Excel Workbook.

In Excel we can accomplish this in two different ways. In this post I will describe the easiest way first, which is less complex. In my next post I will describe the second way.

The less complex one, which is completed in a few keystrokes, is described below:

First we must select the table that we want to transpose and then from the Home tab and from the area of the ribbon named Clipboard we select the command Copy.

01

Once we have copied the selected area, we move the cursor to a cell in a blank part of our worksheet or even select a specific area. Then from the Home tab and from the area of the ribbon named Clipboard we select the arrow below the command Paste, in order for the drop down menu to appear as shown in the image below.

From the drop down menu we select the command Transpose as shown in the image below.

02

From the drop down menu we could also select the command Paste Special.

03

The dialog box Paste Special appears, where we select the command Transpose and press the OK button as shown in the image below.

03a

One way or the other, our data has been transposed. We notice that the Headings of our Columns are now the Headings of our Rows, and what were the Headings of our Rows are now the Headings of our Columns.

04

The disadvantage of this sort of Transpose is the fact that the data of the two tables are not linked. For example if I change the data in a cell of the original table, nothing will get changed in the transposed version of the table.

As mentioned above I will describe in my next post, how to Transpose data that are linked between them.

You can also check out the video below, showing how we can use the Paste Transpose function in order to convert the columns and rows vice verca.

Paste Transpose

Format Multiple Sheets At The Same Time

Many times we have wanted to format more than one sheet in a workbook, so they all look the same way. For example, we want to fill in the first row and first column of the three worksheets with a light red color. We also want, when we type in the cells for the font to be bold, and the text to be justified in the center.

Well, we don’t have to do the formatting as many times depending on how many sheets we have in our workbook.

The procedure is simple and fast. All we have to do is to press Ctrl+click on the tabs of the worksheets that we want to format. Once we do that, the sheets group together, and the color of the grouped sheets turn white as shown in the image below.

01

If we have a hard time using the Ctrl + click, we can also do a right click on one sheet and from the shortcut menu that appears to select the command Select All Sheets, as shown in the image below.

01a

Now, once the sheets are grouped, whatever we enter into one sheet gets entered in the rest of the sheets. Even in we type in something in a cell of a worksheet; the same text will be entered in the same cell of the other sheets. As mentioned above, I want to fill in the first row and the first column of the worksheets with a light red color, and the font of the text to be bold and justified to the center when I type in something.

In the image below I have arranged the worksheets of the workbook to be shown horizontally at the same time.

While the worksheets are grouped together, I select the first row and the first column and do all the formatting that I mentioned above. For example in the image below I have selected the Home tab and then from the area of the ribbon named Font I have selected the Color Fill button and then selected the color that I wanted to use.

02

In the image below I have finished with all the formatting needed and have even typed in the A1 cell of the first sheet the word January. All the formatting and text appear immediately in the rest of the sheets.

03

Once finished formatting group sheets, we should remember to click on the tabs of the sheets to ungroup them, or right click on them and from the shortcut menu that appears we select the command Ungroup Sheets as shown below.

04

You can also check out the video below, showing the steps in order to apply quick formatting to all the sheets.

Save As Workspace

Many times we will find ourselves working with more than one Excel workbook at the same time, and each of these windows are occupying their own separate window. We also know that we will be working with these workbooks for a long period of time, which means that we will have to open these workbooks one by one each time. Process time-consuming and annoying!!!

So what do we do? We save these workbooks as a Workspace.

Once we have saved them as a Workspace, each time that we will be opening the specific file, all the workbooks will appear according to the layout that we had defined.

First of all we must open the workbooks that we are going to use (in my example I will be using three different workbooks).

Once we have opened them, we must arrange them in our screen, so that they all appear at once. In order for us to do that we must select the View tab and from the area of the ribbon named Window, we must select the command Arrange All as shown in the image below.

01

Once the command is selected the Arrange Windows dialog box appears, where we select the layout of our workbooks to appear and press the OK button. In my example I have selected to arrange the workbooks Horizontal.

02

In the image below we can see all three workbooks arranged horizontally.

06

Once we have arranged our windows on our screen, we select again the View tab and from the area of the window named Window we select the command Save Workspace as shown in the image below.

03

The Save Workspace window appears where we much define the location and name of the file. We notice that the extension of the Workspace file is .xlw (Excel Workspace)

05

Once we have defined the location and the name of the file we press the Save button. The next time that we will open the saved Workspace, all three workbooks will appear at once according to the layout that we had defined.

In the image below we can see the icon of the Workspace, which is different from the Excel file.

07

Custom Lists In Excel

In Excel there is a feature that enables us to create a custom list with names, locations, products or whatever else we need. Once created, we can use the AutoFill handle to fill these list items in a workbook.

For example I have typed in an Excel worksheet different sorts of certifications for Office users and I am tired of typing them over and over again every time that is necessary. What can I do?

I can create a Custom List with these certifications, and then by typing only one sequence of these certifications in a cell, and then dragging the AutoFill handle to the rest of the blank cells either horizontally either vertically the list will be completed.

Let’s see how this can be done.

First of all we must type the cells the list of names, locations, etc, that we want to be used as shown in the example below.

01

Once we have typed in the list we must select the list of names and then:

For Excel 2007: We click on the Office button and then we click on Excel Options which is located at the bottom of the menu. The Excel Options dialog box appears, where we select the Popular category from the left of the window and then from the section Top Options For Working With Excel we click the Edit Custom Lists buttons.

For Excel 2010: We click the File tab in order to go to backstage View and then select the category Options. Once the Options dialog box appears we select the Advanced category from the left. Then from the right of the window, we locate the General section and then select the Edit Custom Lists button.

For either Excel 2007 or Excel 2010 the Custom List window appears as shown in the image below:

02

At the bottom of the window and at the right of the area Import List From Cells, we notice the selected area of our cells (in our case $A$1:$A$30). All we have do to now is to press the Import button at the right and we will see our list imported in the Custom Lists dialog box as shown in the image below.

03

Once the list is imported we press the OK button twice in order to return to our spreadsheet. All we have to do now is to go to the cell where we have typed a sequence of our list, and then we move our mouse at the bottom right corner of the cell where we will notice a small black cross appearing where we just drag and drop with our mouse to the area of the cells (this procedure is called AutoFill) that are needed as we can see in the image below.

04

If we hadn’t typed our list from before in our spreadsheet, we could have done it right in the Custom Lists dialog box. All we had to do was to type our list below the area of the window named List Entries, and once finished we would have just pressed the Add button.

Once a list is not needed anymore we can delete it from the Custom Lists dialog box, by selecting the specific list and by pressing the Delete button.

Chart Advisor

Who said that we need to be an expert in Excel to create a professional chart in order to visualize our data?

I believe we have all wasted so much time to find the right chart in order to represent out data in the best way possible. Well this is where the Chart Advisor shows up.

Chart Advisor was created so Excel users can create graphs quickly and effectively. Depending on our data on the Worksheet, it identifies, ranks and displays charts that are mostly relevant to our data in order to create the most appropriate chart. The Microsoft Office Labs has done a successful job once again.

In order to download the Chart Advisor we must follow the address below:

http://www.officelabs.com/projects/chartadvisor/Pages/default.aspx

Requirements: 32-bit Windows XP or Windows Vista. Excel 2007.

Limitations: English only. Analysis limited to 8,000 cells.

For this Post I have installed it on Windows 7 Home Basic and Office 2007 Greek.

Once downloaded, we run the executable file ChartAdvisorSetup.exe in order to install it, where the Chart Advisor Setup window appears.

01

We accept all the privacy policies, until we reach to last window of the setup installation where we are informed that it was successfully as shown in the image below.

04

Now all we have to do is to start Excel, and input all our data that we want represent in a chart. Once we have done we make sure that we have selected the data in our spreadsheet and then weselect the Insert Tab and from the area of the ribbon named Office Labs we select the Chart Advisor button as we can see in the image below.

05a

Straight away the Chart Advisor from Microsoft Office Labs window appears, showing us the best five charts that our data can be represented in, created in seconds.

The first one is the Radar chart as we can see in the image below, and in the middle of the window we can see a preview of how our data will be represented. If we are satisfied with the result we just press the Insert Chart button at the bottom right of the window.

05

The second one is the Pie chart as we can see in the image below, and in the middle of the window we can see a preview of how our data will be represented. If we are satisfied with the result we just press the Insert Chart button at the bottom right of the window.

06

The third one is the Clustered Columns chart as we can see in the image below, and in the middle of the window we can see a preview of how our data will be represented. If we are satisfied with the result we just press the Insert Chart button at the bottom right of the window.

07

The fourth one is the Clustered Bar chart as we can see in the image below, and in the middle of the window we can see a preview of how our data will be represented. If we are satisfied with the result we just press the Insert Chart button at the bottom right of the window.

08

The fifth one is the Line chart as we can see in the image below, and in the middle of the window we can see a preview of how our data will be represented. If we are satisfied with the result we just press the Insert Chart button at the bottom right of the window.

09

At the left of the Chart Advisor window we can either Modify our Chart or we can Filter the Data by selecting the appropriate button. In the image below we can see that we have chose the Filter Data button and then at the top of the Filter Data window we can select or deselect days of the week depending on the data that we have.

10

In the image below I have now selected different data from a worksheet and selected the Chart Advisor button. The Chart Advisor window appears, and as we can see it suggests different sorts of charts at the top row. Then from the left area of the window I have selected the Modify Chart button, and from the Modify Chart area we can do all the settings that we want to for our chart.

11

Whatever settings we do we have to press the Update Chart button that appears in order for the changes to be applied or to press the Undo Changes button to undo all the settings.

12

Once finished we press the Insert Chart button, and straight away our chart has been inserted into to our worksheet as we can see in the image below.

13

Once the chart is inserted we can still modify it, change the layout or format it in order for our chart to look the best way as possible.

%d bloggers like this: