Revision as of 10:36, 9 December 2021

Office

ISO download

Download Office 2019, Office 365 & 2016 ISO Offline Installer. The download file is called ProfessionalRetail.img for Office 2016 Professional and ProPlus2019Retail.img for Office 2019 ProPlus.

How to Extend Office 2019/2016 & Office 365 Trial to 180 Days?

Office home use program (HUP)

Change languages

How to Change the Language in Microsoft Office 2016

Office Location

https://products.office.com/en-us/office-2016-preview. Scheduled 9/22/2015.

For office 2016 & 365, the executables (EXCEL, WORD, ...) are located at C:\Program Files\Microsoft Office\root\Office16. The path can be found by right clicking Excel from the start menu, choose 'More' > 'Open file location'. Then right click the Excel and choose 'Properties'. The 'Shortcut' tab will display the Excel location in the "Target" field.

On Windows 10, I did not see Excel (Under 'E' or under 'M') at Startup menu. Under 'Microsoft Office 2016 Tools' in the start menu, I see Database Compare 2016, Office 2016 Language Pref..., Office 2016 Upload Center, Skype for Business Record..., Spreadsheet Compare 2016, Telemetry Dashboard for Office, Telemetry Log for Office 2016. I did see Word 2016, Publisher 2016, OneNote 2016, Outlook 2016 in the startup menu. Anyway, I can type excel.exe in the search box to launch Excel 2016.

Office 365

Office 365 is a subscription plan to current and future versions of Microsoft Office that offers other productivity services enabled over the internet (Cloud services) including Exchange Online - hosted email for business, and additional online storage with OneDrive for Business.

Try Office 365 for free
NCI and Training

OneDrive

Can I write an equation? Not in Word for the web.

Adding equations to Google Slides. The strategy is to use the online Latex editor to create a math equation, download the file (say in SVG) and insert it into OneDrive/Google documentation. After I insert a picture (SVG file) on top of a diagram, I need to right click the diagram and choose 'Fill' and pick the white color (since the background color is not white). It works fine except I cannot edit the equation.

Intelligent Services

Make Office Work Smarter for You

macOS version

macOS will have another extra menu bar sits on atop/outside the office application. This Mac menu is less implemented in Microsoft Office. We can ignore that part depending on the applications. PowerPoint 2016 for Mac review: New interface and features make PowerPoint pleasant

Outlook

Set up Outlook with Office 365

Videos from office.com

How to Configure Outlook for Mac for Office 365

How to Reduce the Size of Your Outlook Data File

http://www.howtogeek.com/285297/how-to-reduce-the-size-of-your-outlook-data-file/

Different panes

http://biz.colostate.edu/mti/tips/pages/outlookReading.aspx

Navigation pane
Reading pane
- Inbox/Message List pane: See microsoft.com on how to change the font size of the message list. Mail -> VIew -> Current View -> View Settings. Click 'Other Settings' -> Row Font or 'Conditional Formatting' -> one rule.
- Preview pane
Calendar pane: See microsoft.com on how to keep the Calendar in Mail pane. Mouse over (not click) on Calendar located on bottom-left. Click the Pin button to dock it to the right edge of the Outlook window.

Change theme

File -> Options -> General. Select the drop-down list from the 'Color scheme'.

Change view: Messages vs Conversations

Go to View > Conversion.

On the web version, there is no menu bar. The same option is at Filter > Show as. We can also uncheck the option of 'Show Focused Inbox' from Filter.

How to increase the font size in the reading pane

Use ctrl + mouse scroll wheel. This will affect the current email message.

Or use the Magnification bar at the right-bottom corner (on the status bar). There is no way to lock the zoom level for all messages.

Garamond font

https://en.wikipedia.org/wiki/Garamond

How to change all message font size

See this post. The con is the messages have to be converted to plain text (images in the email will not be shown).

Outlook 2010:

Click File > Options > Trust Center (category) > Trust Center Settings... (button) > E-mail Security (category)
Under 'Read as Plain Text', check the box next to 'Read all standard mail in plain text' > click OK once to return to Outlook Options.
Click Mail (category) > Stationary and Fonts... (button).
Under 'Composing and reading plain text messages', click Font... (button).
Adjust the font size/settings as desired (how to you want to see new messages?) and click OK when done, then OK to exit the Signatures and Stationary window, and OK again to exit Outlook Options.

Note - You will have to click off of whatever email you had selected while changing the settings, then click back on it to see the changes you've made.

How to send an encrypted email

https://ocio.nih.gov/InfoSecurity/Policy/Pages/How-to-Encrypt-Email.aspx

Turn an Outlook message into an appointment or a task

Drag the message from the message list to the Calendar or Tasks shortcut near the bottom of the navigation pane.

Out of Office automatic reply

How To Automatically Reply To Specific Sender (Email Address) In Outlook?

Block Out Daily Lunch Time In Outlook Calendar

How To Block Out /Off Time In Outlook Calendar?

New Meeting Request

Click Calendar first and you will see these two options. New appointment will be saved under my own calendar. New Meeting Request will let you to specify To, When, Where, and Subject.

See

Auto delete emails from meeting request

Windows
mac OS - not implemented.

Calendar with email

How to Show a Google Calendar in Outlook

Email Group

How to Create an Email Group and Distribution List in Outlook

Tag emails

How to Tag Your Emails For Maximum Searchability

Recurring emails

How to create a recurring e-mail message in Outlook

What's New

Outlook 2016 for Windows

Excel

65536 rows limit and ? columns limit

Until Office 2007, Excel has a maximum of 65,000 rows. Office 2007 bumped that up to a max of 1 million rows. See https://stackoverflow.com/a/527026
A technical glitch that saw nearly 16,000 Covid-19 cases go unreported in England. Excel's XLS file format dates back to 1987. It was superseded by XLSX in 2007. Had this been used, it would have handled 16 times the number of cases.

Keyboard shortcuts in Excel

F2: edit the selected cell

(MacOS): Control + Option + two fingers on touchpad to Scroll Zoom in/out.

In-cell carriage returns

Alt + return

Autofit column width

Select all ("CMD + a" OR "Ctrl + a")
Menu -> Format -> AutoFit Column Width.

Plot Excel data

How to create 3D Column Chart in MS Office Excel 2016. Select a range of data. Insert -> select an icon showing barplot. Pick a style we like and click on it. We need to modify the chart title as we want.
http://app.rawgraphs.io/

Apply a Color Scale Based on Values

Format -> Conditional formatting
How to Apply a Color Scale Based on Values in Google Sheets
Two ways to change background color in Excel based on cell value, How to use conditional formatting in Excel

Sort data in Excel

Select a range of columns
Click Sort & Filter button and select 'Custom sort'. Select the header of 'Column' of "sort by". Click the OK button. Done

Creating a Drop-Down Filter to Extract Data Based on Selection

Excel Filter Data with Dropdown Arrows. Select any cell (not empty) -> Data -> Filter. Very simple!
Searchable Drop Down List in Excel (Very Easy with FILTER Function). Select cells and use Ctrl+t to create it.

Remove password protection

Remove password protection from Excel sheets using R

Excel has detected that filename is a SYLK file but cannot load it

This problem occurs when the file that you’re trying to open has uppercase letters “I” and “D” as the first two characters.

For example, your file contains:

ID, Patient, Name
100, John, Smith
101, Mike, Doe

To fix it, either update ID to lower case “Id”, or you can add an apostrophe at the beginning of the first line of text.

Prevent auto convert to dates in Excel

Excel Options -> Advanced -> uncheck 'Enable AutoComplete for cell values'.

How to insert a line break in Excel cell

Windows: Ctrl + Enter.
Mac: Option + Enter

Insert a row

It can insert a row above, but not below. See How to insert a row below in Excel 2016

Creating macros

http://www.pcworld.com/article/2880353/5-essential-tips-for-creating-excel-macros.html

Save a macro-enabled workbook

Saving and Using an Excel 2010 Macro-Enabled Workbook. Excel adds the .xlsm extension to the filename.

Open two sheets side by side

Hidden worksheets

http://www.makeuseof.com/tag/unlock-hidden-developer-tab-excel/

7 Fun & Weird Things You Can Create With Microsoft Excel

http://www.makeuseof.com/tag/7-fun-weird-things-can-create-microsoft-excel/

VBA

http://www.makeuseof.com/tag/excel-vba-programming-tutorial/
Tips And Tricks For Excel from extendoffice.com.

How to resize multiple cells to fit pictures quickly in Excel?

How to resize multiple cells to fit pictures quickly in Excel?. It works when I tested on Office 365 Excel on mac.

On mac, we can open VBA by fn + alt + F11 or Tools > Macro > VBA.

Sub ResizePictureCells()
For Each Picture In ActiveSheet.DrawingObjects
  PictureTop = Picture.Top
  PictureLeft = Picture.Left
  PictureHeight = Picture.Height
  PictureWidth = Picture.Width
  For N = 2 To 256
    If Columns(N).Left > PictureLeft Then
      PictureColumn = N - 1
      Exit For
    End If
  Next N

  For N = 2 To 65536
    If Rows(N).Top > PictureTop Then
      PictureRow = N - 1
      Exit For
    End If
  Next N

  Rows(PictureRow).RowHeight = PictureHeight
  Columns(PictureColumn).ColumnWidth = PictureWidth * (54.29 / 288)
  Picture.Top = Cells(PictureRow, PictureColumn).Top
  Picture.Left = Cells(PictureRow, PictureColumn).Left
Next Picture
End Sub

Another technique is to delete pictures. Strangely, 'delete' key does not work on picture objects. Method 1. Click 'F5' or 'fn + F5' on mac. Click 'Special' > check the Object option > OK. All pictures are selected in active worksheet. Press Delete key to delete all of them. Method 2. VBA

Sub DeleteAllPics()
Dim Pic As Object
For Each Pic In ActiveSheet.Pictures
  Pic.Delete
Next Pic
End Sub

Excel print the header row on each page

http://www.guidingtech.com/17822/print-first-row-column-every-excel-page/

Click page layout tab on the ribbon
Switch to the tab for Sheet and spot the section for Print titles. It hosts a row and a column option.
In the 'Rows to repeat at top', enter '$1:$1'.

Done!

How to Print the Gridlines and Row and Column Headings in Excel

http://www.howtogeek.com/220645/how-to-print-the-gridlines-and-row-and-column-headings-in-excel/

How to Insert, Edit, or Remove Page Breaks

How to Insert, Edit, or Remove Page Breaks in Microsoft Excel

How to freeze a row/column or multiple rows/columns in Excel

For example, to freeze the first 2 rows: Select the 2nd row. View --> Freeze Panes.

A simpler way is to move the cursor to some cell, say R4C3. Then 'Freeze Panes' will freeze rows 1-3 and columns 1-2.

Excel Pivot Tables: How to create better reports

https://www.pcworld.com/article/3238667/software/excel-pivot-tables-how-to-create-better-reports.html

Lock cell

How to Lock Cells in Excel

Paste multiple lines into one cell in Excel

3 Quick Methods to Paste Multiple Lines into One Cell in Your Excel Worksheet. The first method (double click the cell works).

Split Cells

How to Split Cells in Microsoft Excel

Split paste text into multiple columns

Use Excel functionality to split the string

Highlight the column containing the data to be converted. Data -> Text to columns. This will call "Convert Text to Columns wizard".

If function

https://support.office.com/en-us/article/IF-function-69AED7C9-4E8A-4755-A9BC-AA8BBFF73BE2

=if(exact(E2, "M"), 1, 0)  # Compare E2 cell and "M" string value, return 1 if TRUE.

Create a plan with Excel

How to Create a Marathon Training Plan With Excel

Excel Add-ins

Some favorites for you to try, and some are free

Remove green triangles

Windows: File > Options > Formulas > untick checkbox Enable background error checking.

Mac: Preferences > Error Checking > untick Turn on background error checking

Hide rows or columns

Split Screens, Freeze Frames, Quick Cut and Paste, Format Painter

http://pcworld.com/article/3153964/business-software/7-excel-tips-for-huge-spreadsheets-split-screen-freeze-panes-format-painter-and-more.html

Office Mobile

Free for devices less than 10.1 inch screen on Windows 10.

https://blogs.office.com/2015/07/29/office-mobile-apps-for-windows-10-are-here/
https://en.wikipedia.org/wiki/Microsoft_Office_Mobile
Caveat Office Mobile只允許使用者儲存為支援Open XML的Office檔案格式，無法儲存成DOC、XLS、PPT格式。

Screenshots from different MS-Excel

2016. The quick access toolbar and menu have a green color as the background. The current selected menu item has white background color so its color is consistent with the Ribbon and the worksheets. The top-left has an 'X' icon as Office 2013.
2013. The quick access toolbar and menu have a white color as the background. The current selected menu item has green background color.
2007. The background color of quick access toolbar, menu and Ribbon is blue by default. There is a big circle Windows icon on the left-most quick access toolbar.
2003 No Ribbon. No quick access toolbar and the location is used to show 'Microsoft Excel' and the file name.
2002/XP or this one. It looks the same as 2000.
2000 Simple.

Use a black color as background for easy reading on screens

On the ribbon go to "Page Layout" and then "Page Color" - select black.

How to Remove the Background From a Picture in Excel

Best Websites to Download Free Excel Templates

Calendar Templates

The Best Free Microsoft Office Calendar Templates for the New Year

Google sheets

4 Google Scripts That Make Google Sheets Much More Powerful

Jump to the last cell in a column

Jump to the Last Cell with Data in a Column in Google Sheets (Hyperlink). Note that to avoid the circular dependency error error, we have to select the cell not in the column we are interested in. For example, if we want to jump to the last row in column B, we can put the =Hyperlink() in any cell in column A.

Create an Automatic Outline in Microsoft Excel

How to Create an Automatic Outline in Microsoft Excel

PowerPoint

Ten simple rules

Ten simple rules for effective presentation slides

Tables

Two big menus

Table Design
Layout: adjust row hight/column width, insert rows/columns, ...

4 Steps for Good-looking Tables in a Presentation

R flextable package. 3.4 PowerPoint or Word documents with officer for powerpoint & officer. Note that the elements of the table is still editable. How to read and create Word Documents in R 2020. It is interesting that if we don't use qflextable() we will get a default pptx table design. So maybe the officer package is all we need.

library(officer)
ft1 <- qflextable(head(airquality))
ft11 <- ft1 %>% theme_box()
ft2 <- qflextable(head(ggplot2::diamonds)) %>% 
        theme_zebra()

ppt <- read_pptx()
ppt <- add_slide(ppt, layout = "Title and Content", master = "Office Theme")
ppt <- ph_with(ppt, value = "Hello world", location = ph_location_type(type = "title"))
ppt <- ph_with(ppt, value = ft1, location = ph_location_left()) 
ppt <- ph_with(ppt, value = ft2, location = ph_location(left = 3, top = 5)) 
print(ppt, target = "~/Downloads/example_table_powerpoint.pptx")

ppt2 <- read_pptx("~/Downloads/example_table_powerpoint.pptx")
ppt2 <- add_slide(ppt2, layout = "Title and Content", master = "Office Theme")
ppt2 <- ph_with(ppt2, value = head(airquality), location = ph_location_left()) # default pptx table style
ppt2 <- ph_with(ppt2, value = ft2, location = ph_location(left = 3, top = 5)) 

# create a temp file
src <- tempfile(fileext = ".png")
# create PNG object
png(filename = src, width = 4, height = 4, units = 'in', res = 400)
# create plot
plot(sample(100, 10))
# save PNG file
dev.off()

# Default image size and location
# the image proportions are distorted
ppt2 <- add_slide(ppt2, layout = "Title and Content", master = "Office Theme")
ppt2 <- ph_with(ppt2, external_img(src),
                use_loc_size = TRUE, 
                location = ph_location_type(type = "body")) 

# Specify the image size in pptx
# PS. specify width, height AND use_loc_size = FALSE in order for it to work
ppt2 <- add_slide(ppt2, layout = "Title and Content", master = "Office Theme")
ppt2 <- ph_with(ppt2, external_img(src, width=4, height=4),
                use_loc_size = FALSE, 
                location = ph_location_type(type = "body"))

# Define the location of the image
ppt2 <- add_slide(ppt2, layout = "Title and Content", master = "Office Theme")
# center the plot
my_width=5; my_height=4.8
s_s <- slide_size(ppt2)
s_w <- s_s$width # width of slides
s_h <- s_s$height # width of slides
left <- (s_w/2) - (my_width/2) 
top <- (s_h/2) - (my_height/2) 
ppt2 <- ph_with(ppt2, value = "Best plot", location = ph_location_type(type = "title"))
ppt2 <- ph_with(ppt2, external_img(src, width = my_width, height = my_height),
                use_loc_size = FALSE ,
                location = ph_location(left = left, top = top))

print(ppt2, target = "~/Downloads/example_table_powerpoint.pptx")

How to Fill Text Color With an Image in Microsoft PowerPoint

https://www.makeuseof.com/tag/fill-text-color-image-powerpoint/

How to Convert PowerPoint Slides to Flashcards for Better Study Results

https://www.makeuseof.com/tag/convert-powerpoint-slides-flashcards/

Powerpoint Presenter View

https://youtu.be/XKRcTw2ATek The screen has to be extended instead of duplicated.
- LibreOffice Impress also support presenter screen. Presenter can use the keyboard shortcut Ctrl+2 to view notes on RHS of your own screen and Ctrl+3 to get an overview of all slides and Ctrl+1 to switch back to the normal view (just see the next slide in PC's screen).
- LibreOffice Impress screenshots (no need to install anything extra at least on Ubuntu 16.04 and actually no need to remember the keyboard shortcuts)
Presenter View in PowerPoint
How to Add and Print PowerPoint With Speaker Notes
Presenter View in Google Slides. Just click in the text box beneath the main slide area to type your notes, or go to Tools > Voice type speaker notes, click the mic button and start dictating. Your notes will be viewable by only when you select “presenter view” from the Present drop-down menu.

How to Black (or White) Out a PowerPoint Presentation

https://www.howtogeek.com/365699/how-to-black-or-white-out-a-powerpoint-presentation/

How to Prevent PowerPoint from Automatically Resizing Text

https://www.howtogeek.com/393935/how-to-prevent-powerpoint-from-resizing-text/

How to Dim the Appearance of Text in PowerPoint

How to Dim the Appearance of Text in PowerPoint
Dim Text in PowerPoint Presentations
How to dim text in PPT 2013 (Video)
Animate bullet points one at a time in PowerPoint for Mac
Search 'animate bullet list'

Aligning Objects Between PowerPoint Slides

How to Blur an Image in PowerPoint

https://www.howtogeek.com/437476/how-to-blur-an-image-in-powerpoint/

How to Insert an Image Inside of Text in PowerPoint

https://www.howtogeek.com/660010/how-to-insert-an-image-inside-of-text-in-powerpoint/

How to Make an Image Transparent in Microsoft PowerPoint

How to Make an Image Transparent in Microsoft PowerPoint, How to Make an Image Transparent in Google Slides

Add Image Placeholders in Google Slides

How to Add Image Placeholders in Google Slides

Insert a right curly bracket

Insert and Format Shapes and Lines in Word 2019 - Office 365
My note on Office 365
1. Insert (Office menu, not the menu bar in OS) -> Shape -> Pick the right curly brace shape
2. Select the curly brace object. Click the 'Shape Format' menu. Click the 'outline' icon (right next to the 'shape fill'). Then pick a color and change/increase the weight

How to Create a Timeline in Microsoft PowerPoint

https://www.howtogeek.com/677677/how-to-create-a-timeline-in-microsoft-powerpoint/

Designer Tool

How To Use The PowerPoint Designer Tool – 10+ PowerPoint Design Ideas For Presentations

Slide master and Design Template

How to Copy a PowerPoint Design Template to Another Presentation

How to Record Your Screen With Microsoft PowerPoint

How to Make a Poster Using Microsoft PowerPoint

https://www.howtogeek.com/680538/how-to-make-a-poster-using-microsoft-powerpoint/

How to Make a Flowchart in PowerPoint

https://www.howtogeek.com/399626/how-to-make-a-flowchart-in-powerpoint/

Remove background

How to Remove the Background from a Picture in Microsoft PowerPoint

Track Changes in a Microsoft PowerPoint Presentation

How to Track Changes in a Microsoft PowerPoint Presentation

Progress bar

How to Create a Progress Bar in Microsoft Powerpoint

Sections

Organize your PowerPoint slides into sections

PDF and PowerPoint

5 Ways to Add a PDF Into a PowerPoint Presentation

Google Slides

Create videos

See Video -> PowerPoint.

Webex and presenter's note

How to properly present PowerPoint slides in Webex Meetings. However I don't have PPT Notes option on my mac's Webex; I only see Notes.

Word

Read to you

5 Ways to Make Your Computer Read Documents to You

Double space

How to Double Space a Word Document

How to Remove Empty Lines

How to Remove Empty Lines from a Word Document

Hide spelling and grammar errors in Word

How to Hide Spelling and Grammar Errors in a Specific Word Document

How to Add and Remove Comments in Word

Rotate a page in Word files

3 Microsoft Word Formatting Tricks You Should Really Know

Avoid change simple quote ' to smart quote ’ (autoformatting) in Word

Uncheck two options in Options -> Proofing -> Autoformatting.

8 MS Word Templates That Help You Brainstorm & Mind Map Your Ideas Quickly

http://www.makeuseof.com/tag/8-ms-word-templates-brainstorm-mind-map-ideas-quickly/

How to use Word to create holiday cards and other projects

https://www.pcworld.com/article/3146255/software/use-word-to-create-holiday-cards-and-projects.html

Avoid broken hyperlinks from web address containing space

http://www.howtogeek.com/213624/how-to-create-a-live-hyperlink-from-a-web-address-containing-spaces-in-a-word-2013-document/

Track changes and comments

How to make sure all the changes have been accepted or rejected and all comments have been removed?

How to Make a Dot Grid Paper Template in Microsoft Word

Outline view

How to Use Outline View in Microsoft Word

Google Docs

How to Hide or Remove Comments in Google Docs

Teams

Flow Charts

To create a flow chart in Word,

View > Gridlines
Insert > Shape/Line or Text Box

Note that though I can create flowcharts in Word or PowerPoint, I can not export the flowchart to a png file from Word or PowerPoint on my Mac. So I end up using LightShot to take a screenshot.

But Word highlights words with mis-spelling:( The fix that, click 'Review' > Spelling and Grammar. Click 'Ignore' for every words it finds.

Powerpoint