MODULE 1: PLAYING EXCEL WITH KEYBOARDS Having Excel keyboard shortcuts at your fingertips simplifies complex processes. Shortcuts create efficiency and promote accuracy. Spend more time analysing the data and less time processing it.
• Basic and advanced cell selections
• Format cells
• Apply basic borders to cells
• Formulas
• Manage multiple worksheets
• Pivot tables
• Auto filter
• Adjust column width and row height
MODULE 2: DATA VALIDATION Create reports and insights which include a self-error handler. Data validation maintains the integrity of the data when accessed by other users. Create customised rules, and drop down lists to assist users to efficiently modify reports without rick of changing raw data.
• Create a drop-down list of items in a cell
• Restrict entries, such as a date range or whole numbers only
• Create custom rules for what can be entered
• Prevent duplicates
• Restrict to specific date range
• Restrict to dynamic date range
• Prohibit weekend dates
MODULE 3: PIVOT TABLES Pivot tables are one of Excel’ s most powerful features. A pivot table allows you to extract specific data from a large and detailed data set. A pivot table can tame hundreds of thousands of data cells into a manageable application with click and drag functionality.
If you are finding yourself writing lots of formulas to summarize data in Excel then Pivot Tables can save you a lot of time and work and give you insights into your data that are otherwise too hard to discover.
MODULE 4: PROTECTING YOUR SPREADSHEET If you are working with confidential data, you might want to password protect your entire Excel file. Though Excel security is not foolproof, but in most of the cases is a good enough security option. If you don’ t want to password-protect the entire Excel file, you may use the following options:
• Locking / Unlocking Specific Cells
• Selectively unlock the cells that are supposed to be edited by someone, leaving the rest locked.
• Setting Ranges with Passwords
MODULE 5: RECORDING A MACRO Macros are small programs that run within Excel and are used to automate common repetitive tasks. Using macros, you can save hours and boost productivity manifold. Macros are one of Excel’ s most powerful features, yet most of the users fail to utilise it fully. A macro can be designed as the recording of a series of tasks, so that it can be automated. It’ s the simplest form of automation – showing a software program the steps to be followed to get something done, and using that recorded macro to automate that process. Macros in Excel are written in Excel VBA( Visual Basic for Applications). This is a version of Visual Basic( a prominent Microsoft programming language) developed specifically for use in Office-like applications.