Excel Tips – Data Functions

The following document explains some tips for using excel and manipulating data pulled from a datatel query. While this is in the context of a Datatel query, these tips will work with any excel file.

 

Navigating a Spreadsheet

Ctrl + Home – takes you to cell A1 from anywhere

Ctrl + End – takes you to the last cell from anywhere

Home – Moves to the beginning of the row

Ctrl + Up Arrow – Moves to the top of a column

Ctrl + Down Arrow – Moves to the bottom of a column

Ctrl + Right Arrow – Moves to the end of a row

 

End then Down arrow – takes you down to the next unlike cell

End then Up arrow – takes you up to the next unlike cell

Ctrl + Home – Moves to the beginning of the Sheet

Ctrl + End – Moves to the end of the Sheet

End then Left arrow – takes you left to the next unlike cell

End then right arrow – takes you right to the next unlike cell

**** Note: Use ANY of the above cursor movement keys while holding down the shift key and it will select the data

 

Using D or data Functions

 

=DSUM()

 

The DSUM function lets you add together numbers based on a selection criteria. For  example if I wanted to sum all of the Tuition for Freshman, I could sum based on the class year. To do this you need the DSUM function as well as a criteria block.

 

Criteria Looks something like this

AWARD

FIRST

SL

Mary

 

Award Amount

>2000

 

The function looks like this

=DSUM(A1:I100,9,L1:M2)

 

Where A1:I100 is the table range in the spreadsheet. It includes all data and headers

 

9 is the column to be summed. In this case the ninth column from the left within the data range

 

L1:M2 is the cells containing the criteria as displayed above.

 

The result of this function will, in this case sum column 9 (award amounts) when the AWARD =SL and FIRST=Mary

 


Other Criteria

 

AWARD

FIRST

SL

 

 

Mary

 

The above is a variation and will give a completely different result. It says to sum column 9 (award amont) when AWARD=SL OR FIRST=Mary. The range is now L1:M3

 

=DCOUNT()

The DCOUNT function lets you count non-blanks based on a selection criteria.

The function looks like this

=DCOUNT(A1:I100,9,L1:M2)

 

The criteria is the same as for DSUM. You set your criteria and specify the range within the function.

 

=DAVERAGE()

 

The DAVERAGE function lets you average values based on a selection criteria.

The function looks like this

=DAVERAGE(A1:I100,9,L1:M2)

 

The criteria is the same as the others. You set your criteria and specify the range within the function.

 

Call Out Text Bubbles

 

Call Out text bubbles can be used to draw attention to an item on your spreadsheet. You can pick a shape, set its size and fill it with text. You can also change its color.  To set a callout:

 

Look for the drawing tools at the bottom of your screen. If they are not there, Click on the View menu, then on Tool Bars, then on Drawing Tools .

 

On the Drawing Tools tool bar, click on AutoShapes

 

Click on Call Outs

 

Click on the shape you want

 

In your spreadsheet click and drag to draw the shape where you want it.

 

You can, move and resize the shape by dragging it. You can also move the Pointer by dragging it as well.

 

Click inside the call out and type in the text

 

You can set the font size and style, color using the font options on the tool bar.

Can't find what you're looking for? Email us at itknowledgebase@whitman.edu and let us know!