Excel Stuff

Become better
at using Excel

Cool shortcuts

CTRL + SHIFT + & Apply border
CTRL + SHIFT + _ Remove border
CTRL + SHIFT + ~ Gen number format
CTRL + SHIFT + # Date format
CTRL + SHIFT + % % format
CTRL + F1 Hide / disp ribbon
ALT + SHIFT + F1 New sheet
F8 Extend selection
F4 Switch $ for select range SHFT + F8 Select non adj cells
ALT + SHIFT + F10 Disp error button
ALT + N + V Pivot Table
ALT + E + S + V Paste as value

Pivot Tables

Pivot Tables
- Use slicers
- Use Timeline Slicers
- Use table formats for PT
- Add blank rows after item for more clarity

Cool formulae

- Mod formula + conditional formatting
- Formula helper
- Array formula
- What if analysis
- Match index instead of vlookup
- Sumproduct (sumif / countif)
- Addons.

Things you should know

Useful stuff:
- Keyboard shortcuts
 - F9 to validate (works also for arrays)
- Pivot Tables + Get piv data formula
- Flash fill vs text delimited
- Shut that shouting grandpa in excel
- Set gridlines to white
- Copy sum selected macro
 - XLSB is in binary format, which makes files smaller and faster to open than XLSX (although you won't see much of a difference for small files). The only thing is, you can't tell if the file contains macros (unlike XLSM)
 - XLXS files are larger than XLSB, and they also don't compress as well as XLSB
 - XLS is the old format from Excel 2003, don't use it if you don't have to.
 - XLSB and XLSX are in fact Zip files - add .zip at the end of the file name
- Macro recorder
 - Replace formula by value in macro
- Flatten data with F5 special + ctrl+ enter
- Nice formulae:
  - Vlookup
   - concatenate several columns for unique criteria
   - index match http://www.businessinsider.com/excel-tricks-vlookup-index-match-pivot-tables-array-2013-5?op=1
  - Sumif
  - and best of all sumproduct
  + other array formulas
" Array formulas such as sumproduct or formulas in curly brackets will allow you to perform single cell calculation rather than doing step by step calculations in multiple columns, and can be a good alternative to pivot tables" This is highly context dependent, so sometimes the best solution is NOT an array formula.

Books about Excel

- Books I read:
  - CTRL + SHIFT + Enter Mike Girvin (super awesome)
  - Pivot table bill Jelen (not too bad - the most intersting is the last chapter)
  - Formules Excel en Francais (it's crap don't read it)

Cool Excel online resources

- Websites
 - Chandoo
  - Slaying excel dragons youtube channe
- Organiser en rubriques: formules, format, navigation, automatisation, autres
Other stuff

Record how long macro takes to run
Performance: SSD drive will open your workbook MUCH faster than a hard disk Retour vers le haut