Set up Excel for Data Analysis

Below are few tips to set up Excel for Data Analysis:


How to create table and name it?
  1. In Excel Tab, click on any cell with data in it and press Ctrl +T. Excel will automatically select all columns and rows that have data. Click OK to create Table.
  2. In Tab, click on any cell that have data. In Top Menu Bar, select Design Tab. In top left hand side of screen, find table name option to change name from Table1 to Name you want to change to.
How to activate Excel Solver add-in?
  1. Select File - Options - "Add-Ins" Option and click GO button
  2. Check the box labeled "Solver Add-in" and click OK
  3. Navigate to Data Tab and see new button for Solver Add-in

How to enable Developer Tab to view macros?


  1. Select File - Options - Click "Customize Ribbon"
  2. On the right-hand side of the Excel Options dialog box, check the Developer checkbox and click OK
  3. Check that DEVELOPER menu has been added to the ribbon.


Shortcuts:

# ALT+F11 - To invoke VBA Editor or Toggle back to Excel


Shortcut keys
Description

Ctrl + ; (semicolon)
This enters the current date

Ctrl + I
This italicizes the text
Ctrl + 1
This displays the Format Cells dialog box
Ctrl + T
This displays the Create Table dialog box
Alt + E + S + V
This pastes values only
Alt + E + D
This deletes the selected cells
Shift + Space
This selects the entire row
Ctrl + Space
This selects the entire column
Ctrl + E
The flashes the Fill menu
F2
This edits the active cell

Alt + O + C + A
This resizes the width of the selected cell(s)
Shift + Ctrl + Arrow key
This highlights all the rows with values in the current column
Ctrl + Down arrow key
This goes to the last cell with data
Shift + F11
This creates a new sheet
Alt + F11
This opens the VBA Editor
Alt + F8
This opens the Macro window
Ctrl + Y
This repeats the last action taken
Steps to create VBA code
1. Open VBA editor
2. Create Module

Popular posts from this blog

Cognos Excel Text Wrapping Issue - Row Merge and Column Merge

Below are the properties to set to keep column size and width with text wrapping: Column Merge issue  http://www-01.ibm.com/support/docview.wss?uid=swg21674298 Set the advanced server property RSVP.EXCEL.XLS2007_COLUMN_WIDTH_CONTROL to TRUE for both BatchReportService and ReportService services.        Row Merge issue http://www-01.ibm.com/support/docview.wss?uid=swg21440556 RSVP.EXCEL.XLS2007_ALLOW_WRAPPING_SINGLE_CELL also to be set to TRUE  for both BatchReportService and ReportService services.   Once done, you can set any column width to be fixed and see same result in Excel as well as HTML. Please note that above properties will not be honored if you have unchecked Grouping repeating cells Report property in Report Properties menu. 
Read more

Cognos: How to replace an existing report without breaking links?

Below IBM document explains behavior of copy/move or overwriting impact of objects:- https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.wig_cr.doc/c_gtstd_copy_move_items.html If you copy and overwrite an existing report, the copied report ID and links replace those of the existing report. In this case, you might need to update links, such as links to job schedules. If you move and overwrite an existing report, the existing report ID and links are maintained. In this case, references to the moved entry are broken. However, this doesn't work in all cases and we see drill through links broken in some cases. Best approach in such case is to use cognos deploy method where you update reports in separate folder in same environment and import it to destination location. You can create single export and import tasks to use for all such updates.
Read more

How to change package for a report in IBM Cognos Analytics (ICA)?

In Cognos 10.x and 8.x , this was the simple to handle on Cognos Connection or via Repot Studio interface.  In Cognos 11.0.6, You  need to load classic view (Cognos Connection) to go back to this interface to change the package. In Cognos Cloud environment, we have single environment for Development and UAT. In such cases, when we move content from Dev folder to UAT area, we need to switch to classic mode to change the package.
Read more