Effective Excel and Other Tools
Razzi Abuissa
2013-08-01
Excel: see demo.xlsx
Use the ' character to prevent formatting,
control/shift click to select,
paste special to get values, not formulas
Ranges and Functions
Named Ranges,
conditionals,
concatenate,
match
Data and Developer tabs
Remove duplicates,
Enforce valid data
Macro recording and playback
Check out video tutorials on lynda.com
PowerShell
Manipulate the windows file system
cd, dir, ps, rm, tree
Change directory, list files in directory, see processes, remove file, see file tree
Save the names of files in a directory as a txt file:
PS C:>
dir -name > file_names.txt
Run a python script:
PS C:> python hello_world.py
Check out Microsoft documentation, Google a lot
Python
High level free open source programming language.
-
Can parse .pdf, .doc, .html, anything
-
Can connect to sql databases, the web, and SAP
-
Runs in servers, desktop applications, and clusters
- Can send emails and text messages
Python has powerful features to deal with data, Unicode support for working with non-Latin language, and an active community with many free modules.
Check out udacity.com's Intro to Programming,
take Codecademy's Python track
Putting it all together
part 1:
Look up a list of chemicals by their CAS number on Canada's online DSL portal, save all information to an Excel spreadsheet, and open it for further analysis
see dsl.py
Techniques used: threading, file I/O
3rd-party: Selenium, win32com
Putting it all together
part 2:
Merge rows in an SAP dump to consolidate information, like a flexible and powerful pivot table
see merge_rows.py
Uses DataNitro, a commercial Excel add-on
Tools for Productivity
By razzi
Tools for Productivity
- 909
