Excel Configuration and Setup

Personalized setup and configuration for Microsoft Excel.

• See my xl-macros repository for details on the VBA code used in my personal macros.

Installation

• Download Office365 ISO from Microsoft Account Website.
  • Ensure to select 64-bit installer
  • Select option to download an Offline Installer for usage in the future if necessary.

• Once installer downloads, mount the ISO, extract the zipped archive and run the 64-bit installer under a separate folder.
• Install Office365 and restart machine.
• Open any office application and select Office Account > Office Insider and join the Beta Channel.
• Update Office365

Excel Setup

PERSONAL.xlsb and Macros

• Clone Excel repository from Github: jimbrig/excel-config

git clone [email protected]:jimbrig/excel-config.git
cd excel-config

Note that this repo has a submodule for xl-macros repo.

• Open Excel and turn on AutoSave
• Copy and Paste PERSONAL.xlsb into https://d.docs.live.net/c58858cfd97d35ae/Documents where OneDrive stores office files.
  • Alternatively, create a dummy Macro via Developer > Record Macro > Personal Workbook and then unhide via View > Unhide > PERSONAL.xlsb.
• Add macros to PERSONAL.xlsb via Alt + F11 and import Main.bas
• Save PERSONAL.xlsb and close excel.

Local Path for PERSONAL.xlsb: %appdata%\Microsoft\Excel\XLSTART

or

C:\Program Files\Microsoft Office\root\Office16\STARTUP

Add-Ins

First, Enable Developer Tab in Ribbon

Links: https://exceldashboardschool.com/how-to-install-an-excel-add-in/

Excel Add-Ins

Path: C:\Users\jimbr\AppData\Roaming\Microsoft\AddIns

• Solver
• Analysis Toolpak
• PowerPivot
• Forecast Wizard

Actuarial Add-Ins

• Function.xla
• Toolbar Add In.xlam
• Tools.xlam

COM Add-Ins

Path: C:\Program Files\Microsoft Office\root\Office16\ADDINS

• Microsoft Data Streamer for Excel
• Microsoft Power Map for Excel
• Microsoft Power Pivot for Excel

Store Add-Ins

Manage from here.

• Money in Excel
• Excel-to-Word Document Automation
• Explore the Data
• Mini Calendar and Date Picker
• Wikipedia

New Downloaded Add-Ins

Note: For these downloaded add-ins, will want to move to a trusted location and unblock the add-in under Security Settings.

• DataXL
• DataFX
• Power-User
• X-Y Chart Labeler
• Favorites Bookmarks

Customization

Quick-Access Hotkeys and Toolbar

Load the configuration file under: xl-config/Excel Customizations.exportedUI.

In order,

1. Paste Special
2. Toggle Read Only
3. Name Manager
4. Macros
5. Page Setup
6. Toggle Filters
7. Add/Remove Filters
8. Pivot Table
9. Refresh All
10. Calculate
11. Datasheet Formatting
12. Email with Attachment
13. COM Add-Ins
14. Excel Add-Ins
15. Autosave Toggle

If make any changes ensure to backup in the .ExportU file under Program-Settings/Excel and push to this Git repo!

Templates

• https://www.goskills.com/Excel/Resources/Free-Excel-templates

Jimmy Briggs