Excel Tips

Phone: 760-707-6718

E-mail: webmaster@easybuttoncharts.com

Tips and Formulas:

SHORTCUTS

Copy = Select area, Ctrl C (copy is not always an option when right clicking the mouse)
Paste = Ctrl V
Go to beginning of a row = Home key
Go to end of a row = End key
Go to Cell A1 = Ctrl Home
Go to last cell of the last row and column of data = Ctrl End
Highlight column = Ctrl Spacebar
Highlight row = Shift Spacebar
Next sheet = Ctrl Page Down
Copy a sheet = Ctrl Drag
Apply filter to selected row (field names) = Press and hold Alt,D,F,F (Data, Filter, AutoFilter)
Refresh Pivot Tables = Click on the pivot table then click the ! icon (OR) Use VBA code
             Right click on the sheet tab with the pivot table, Choose View Code, Type:
             Private Sub Worksheet_Activate()
                Active.Sheet.PivotTables(1).PivotCache.Refresh
             End Sub

PERCENTAGES

=SUM(PASS/TOTAL)

    A                        B                       C                      D                    E                               F                               G
1  First Name         Last Name       Pass                 Wrong/Fail     Total Questions        Percentage               Student ID
2  Kyle                   Sneller              95                     5                     100                           95%                          2SZ12325
3  Bella                  Piner                 96                     4                     100                           96%                          6ORPK015
4  Cindy                 Sneller              97                     3                     100                           97%                          9SZ15987

=SUM(C2/E2) which would be =SUM(95/100) with an answer of .95.  To show it as a percentage, either click the % icon on the toolbar, or click Format, Cells, Number Tab, Click Percentage.

For same result:
=SUM(-((D2/E2)-1))  Helpful if you don’t have a field for the number passed.

=IF(C2>89.9,”A”,IF(C2>79.9,”B”,IF(C2>69.9,”C”,IF(C2>59.9,“D”,”F”))))

OTHER

=CONCATENATE(A2,” “,B2) would be =CONCATENATE(Kyle,” “,Sneller) with a result of Kyle Sneller.  (OR) =A2&” “&B2 with result of Kyle Sneller.

=EXACT(C2,E2)  (If the results are the same in both cells the answer will be “TRUE” otherwise it will be “FALSE.”)

=MID(G2,4,2) Results = 12  This can be used where the first digit of “2” is the school number, Digits 2-3 of “SZ” are the alphabet range for the student, Digits 4-5 represent the grade, and Digits 6-8 equal the student number within the grade.

=LEFT(G4,1)  Results = 9.  This would produce the school number using the Student ID naming convention shown above.

=RIGHT(G3,3) Results = 015.  This would produce the student number within the grade using the Student ID naming convention shown above.

=NETWORKDAYS(StartDate,EndDate,Holidays) Create a list with the holidays. Select the list and Click Insert, Name, Define and type in Holidays.  For StartDate and EndDate either enter a Date or the cell reference with the date in it.  This will count working days only.

=COUNTIF(B2:B4,”Sneller”)  Result = 2
=COUNTIF(A2:A4,”Kyle”) Result = 1
=COUNTIF(A2:A4,”Cindy”)+COUNTIF(A2:A4,”Kyle”)  Result = 2  (Multiple Criteria)
=COUNTA(B2:B4)  Result = 3 (This would count the number of students.)
=SUM(C2:C4) Results = 288. 

Pivot Tables
Select the data and the field names.  (Each column must have a field name)
Click, Data, PivotTable and PivotChart Report, Next, Next, Layout
Then drag the fields you want on the grid.  Place the field name at “ROW” and the corresponding sum at “DATA,” and then click “OK” and then click “Finish”

VLOOKUP (Look up a code in your data against a table with the code and the description in another set of data)

=VLOOKUP(G2,StudentIDs!$A:$E,3,FALSE) 
The “3” represents the third column over.  Which would be column “C.”  Let’s say that on a sheet named “StudentIDs” column “A” contains the student IDs and column C contains the students’ phone numbers and G2 is the student ID on our current page. This formula would return the phone number for Kyle Sneller using the data in the table at the top of the page.

ELIMINATE ERROR RESULTS

=IF(ISERROR(C2/E2),0,C2/E2)
=IF(A2=0,””,A1/A2)
=IF(ISNA(VLOOKUP(B14,$C$2:$D$68,5)),"",VLOOKUP(B14,$C$2:$D$68,5))

To contact us:

Charts just a click away