Excel Tips: Headers, Footers, Marcos (Updated)

1. Adding a File Path and Fileanme
If you need to show the current workbook's filename and path in a header or footer, you can use the following
Path: &[Path]
File Name: &[File]
Page: &[page]
Total pages: &[pages]
Name of worksheet: &[tab]

If you need to insert the file path and file name into a cell, try the following
=CELL("filename")
If you want to strip out the brackets and the sheet name, you can use the following variation on the formula:
=SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[","")

2. Show the "Ampersands &" in header or footer, you need to &&
e.g. Tom && Jerry
it will be shown as "Tom & Jerry"


3. Add auto "last modified date"
You need to have a macro as the following

Private Sub Workbook_SheetChange(ByVal _
  Sh As Object, ByVal Target As Excel.Range)
    ActiveSheet.PageSetup.CenterFooter = _
      "Worksheet Last Changed: " & _
      Format(Now, "mmmm d, yyyy hh:mm")
End Sub

Or the following as last saved date

Private Sub Workbook_BeforeSave(ByVal _
  SaveAsUI As Boolean, Cancel As Boolean)
    Dim sht
    For Each sht In Sheets
        sht.PageSetup.CenterFooter = _
          "Workbook Last Saved: " & _
          Format(Now, "mmmm d, yyyy hh:mm")
    Next

End Sub

With font and font size assigned

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sht
    For Each sht In Sheets
        sht.PageSetup.RightHeader = _
          "&""Calibri""&8" & _
          "Updated on: " & _
          Format(Now, "mmmm d, yyyy")
        
    Next
End Sub

The best way to accomplish the task above is to add a macro to the ThisWorkbook object that is triggered just before a workbook is saved.

For Windows, how to create a Macro
https://support.office.com/en-us/article/Create-or-delete-a-macro-2841ba85-9da6-4bd7-93a4-0da90844f283?ui=en-US&rs=en-US&ad=US&fromAR=1#bmcreatemacrovba

Missing Drop Down Arrows in Pull down Cell

In Windows,

"Missing arrows" is a fairly common problem, and usually they can be fixed by using the keyboard shortcut to show objects -- Ctrl + 6

Can fix the issue.


How about Office in Mac?

Where is the hash key

Where is the hash key from the Apple Keyboard connecting to iPad?


Ans: Alt + 3 (press alt and 3 key)

Useful Shortcuts in Mac OS X

1. In Safari, go to top of the page or go to bottom of the page
1. Shortcut: Command + "Up arrow" or Command + "Down arrow
2. In Safari, shortcut to tabs
1. Shortcut: Command + "number"
3. Fine tuen the volume -- by pressing F11 or F12, the system will increase or decrease the volume level by one unit, if you want to have a fine adjustment, the following shortcut will take one quarter unit of volume adjustment.
1. Shortcut: Option + Shift + F11 or F12
4. Show the desktop 
1. Command + F3
5. Delete a word 
1. shortcut: Option + Delete

How to show Chinese Calendar in Calendar app in Mac

View the lunar calendar

Choose Calendar > Preferences, click General, click the “Show alternate calendar” pop-up menu, then choose a lunar calendar.

Excel Useful Formula #1

1. Find the repeated contents: =if(countif(A:A,A2)>1, "Repeated content", "")


2. Formula: =TRUNC((DAYS360(D1,today(), FALSE))/360, 0)

D1: is your target cell for input date of birth
today(): is the formula to return the date of today

3. Ranking: =rank(K2, K$2:K$56) ranking of 55 students results

4. Grading: =if(K2>=85, "A", If(K2>=74, "B", IF (K2>=60, "Pass", "Fail")))

5. Count with condition:

=countif(B2:B56, "80")

=countif(B2:B56, ">=80")

Sorting in OSX

In OSX, sorting and arranged by is different.

• View > Arrange By > None (^⌘0)
• Hold Option, View > Sort By > Name (^⌥⌘1)

Try the above commands, you will see the difference!