Jump to content
One on One Extensive Advanced Excel Training ×
Business Intelligence & Analytics for Digital Transformation

Saurabh Jain

Administrators
  • Posts

    1,724
  • Joined

  • Last visited

  • Days Won

    61

Blog Entries posted by Saurabh Jain

  1. Saurabh Jain
    Petal turns your documents into a digital expert you can talk to!
    Get answers quickly by talking to your personalized ChatGPT. Upload research papers, technical manuals, policy handbooks, resumes, case studies, or even works of fiction. Chat with multiple documents at once and track sources. Collaborate with your team and share files, annotations, and comments.
    Supports PDFs, Word, Powerpoint, and other common text files.
    Recognized as a trusted resource by MIT libraries.
    Sign up for free! https://www.petal.org/
     
  2. Saurabh Jain
    CTRL+SHIFT+8 => Selects the entire table,
    including the header row and the total row (if present)
     
    2)   CTRL+SHIFT+T => (a toggle shortcut):
    Adds/removes the total row of a table
     
    3)   CTRL+SHIFT+U => Expands/contracts the Formula Bar (toggle)
     
    4)   CTRL+SHIFT+9 => unhides hidden rows within selection
     
    5)   CTRL+SHIFT+L => activates/deactivates filtering (toggle)
     
    6)   CTRL+SHIFT+F6 => toggles to the previous workbook (when two or more workbooks are open)
     
    7)   CTRL+SHIFT+F4 => Repeats the last search (like: Find Next)  - but backwards (upwards)
     
    😎   CTRL+SHIFT+< => copies values from cell above (same as: CTRL+D) but without selecting the cell above (works on more than one cell)
     
    9)   CTRL+SHIFT+> => copies values from cell to the left (same as: CTRL+R) but without selecting the cell to the left (works on more than one cell)
     
    10)  CTRL+SHIFT+SPACEBAR => same as CTRL+A
     
     
    11)  CTRL+SHIFT+O  => selects all cells containing comments (notes in 365)
     
    12)  CTRL+SHIFT+PAGEDOWN => Selects the current and the next worksheet (creation of a Group)
     
    13)  CTRL+SHIFT+PAGEUP => Selects the current and the previous worksheet (creation of a Group)
     
    14)  CTRL+SHIFT+[ => Selects all Precedents (Direct & Indirect)
     
    15)  CTRL+SHIFT+] => Selects all Dependents (Direct & Indirect)
     
    16)  CTRL+SHIFT+7 => Applies border (single, black, thin) to selection (only outer border is applied)
     
    17)  CTRL+SHIFT+- => removes border (single, black, thin) from selection (removes all borders within the selection)
     
    18)  CTRL+SHIFT+A =>  (when the insertion point is next to the function's name entered) Displays the function’s arguments (in parentheses)
     
    19)  CTRL+SHIFT+' => Copies the value from the cell above the active cell into the active cell. If the cell contains a formula, it is converted into value.
     
    20)  CTRL+SHIFT+. => Copies the contents of the adjacent cell (to the left) into the active cell. If the adjacent cell contains a formula it copies the formula with relative references.
     
  3. Saurabh Jain
    Download the dataset for practice
    https://bi-analytics.org/files/file/314-dataset-for-tutorial-1-for-poer-bi/
    Assignment  & Instructions
     
    1 create the visual by your hand - once done - pl comment Done- You can also share the screenshot : Choose better colours 
    Additionally now try the following
    2 Create a chart - showing Sales by year 
    3 Add Qty in Tooltips : You can now see sales and Qty both in tool tip when you hover a mouse on the column of any year.
    Do comment what you learnt in the tutorial ?
  4. Saurabh Jain
    The first step is to install the power BI on your machine 
    There may be many ways to Install (Download) Power BI and as always you can google "Download Power BI" 
    Once done , pl comment Done 

     
    It will be pleasure to know your location as well... or simply you can keep your profile up to date 
  5. Saurabh Jain
    Improving your Excel skills is a great place to begin to claw back a few minutes on every project, because Excel is a tool used by most of us on a regular basis. It has so many incredible capabilities that are not immediately apparent. Just finding one trick can save you minutes every day.
    Excel Tip No. 1: Automatically SUM() with ALT + =
    Quickly add an entire column or row by clicking in the first empty cell in the column. Then enter ALT + ‘=’ (equals key) to add up the numbers in every cell above.

     
     
    Excel Tip No. 2: Logic for Number Formatting Keyboard Shortcuts
    At times keyboard shortcuts seem random, but there is logic behind them. Let’s break an example down. To format a number as a currency the shortcut is CRTL + SHIFT + 4.
    Both the SHIFT and 4 keys seem random, but they’re intentionally used because SHIFT + 4 is the dollar sign ($). Therefore if we want to format as a currency, it’s simply: CTRL + ‘$’ (where the dollar sign is SHIFT + 4). The same is true for formatting a number as a percent.

     
    Excel Tip No. 3: Display Formulas with CTRL + `
    When you’re troubleshooting misbehaving numbers first look at the formulas. Display the formula used in a cell by hitting just two keys: Ctrl + ` (known as the acute accent key) – this key is furthest to the left on the row with the number keys. When shifted it is the tilde (~).

     
    Excel Tip No. 4: Jump to the Start or End of a Column Keyboard Shortcut
    You are thousands of rows deep into your data set and need to get to the first or last cell. Scrolling is OK but the quickest way is to use the keyboard shortcut CTRL + ↑ to jump to the top cell, or CTRL + ↓ to drop to the last cell before an empty cell.

    When you combine this shortcut with the SHIFT key, you’ll select a continuous block of cells from your original starting point.
     
    Excel Tip No. 5: Repeat a Formula to Multiple Cells
    Never type out the same formula over and over in new cells again. This trick populates all of the cells in a column with the same formula, but adjusts to use the data specific to each row.
    Create the formula you need in the first cell. Then move your cursor to the lower right corner of that cell and, when it turns into a plus sign, double click to copy that formula into the rest of the cells in that column. Each cell in the column will show the results of the formula using the data in that row.

     
    Excel Tip No. 6: Add or Delete Columns Keyboard Shortcut
    Managing columns and rows in your spreadsheet is an all-day task. Whether adding or deleting, you can save a little time when you use this keyboard shortcut. CTRL + ‘-‘ (minus key) will delete the column your cursor is in and CTRL + SHIFT + ‘=’ (equal key) will add a new column. From an earlier tip, think about CTRL + ‘+’ (plus sign).

     
    Excel Tip No. 7: Adjust Width of One or Multiple Columns
    It’s easy to adjust a column to the width of its content and get rid of those useless ##### entries. Click on the column’s header, move your cursor to the right side of the header and double click when it turns into a plus sign.

     
    Excel Tip No. 8: Copy a Pattern of Numbers or Even Dates
    Another amazing feature built into Excel is its ability to recognize a pattern in your data, and allow you to automatically copy it to other cells. Simply enter information in two rows which establish the pattern, highlight those rows and drag down for as many cells as you want to populate. This works with numbers, days of the week or months!

     
    Excel Tip No. 9: Tab Between Worksheets
    Jumping from worksheet to worksheet doesn’t mean you have to move your hand off the keyboard with this cool shortcut. To change to the next worksheet to the right enter CTRL + PGDN. And conversely change to the worksheet to the left by entering CTRL + PGUP.

     
    Excel Tip No. 10: Double Click Format Painter
    Format Painter is a great tool which lets you duplicate a format in other cells with no more effort than a mouse click. Many Excel users (Outlook, Word and PowerPoint too) use this handy feature, but did you know you can double-click Format Painter to copy the format into multiple cells? It’s quite a time-saver.

     
    More Excel Tricks or Help requiered
    Please ask your questions in http://bi-analytics.org/forum/1-excel-fundamentals/
    What tricks do you employ to make the most of your time with Excel? Feel free to share in the comments so we can all benefit!
  6. Saurabh Jain
    Download file 
    https://bi-analytics.org/files/file/312-rank-function-in-excel/
     
    The Excel RANK.AVG function returns the rank of a number against a list of other other numeric values. When values contain duplicates, the RANK.AVG function will assign an average rank to each set of duplicates.
    The Excel RANK.EQ function returns the rank of a number against a list of other other numeric values. When values contain duplicates, RANK.EQ will assign the higher rank to each set of duplicates.
     
     
×
×
  • Create New...