Pre Test & Post Test MS Excel
Pre Test MS Excel Post Test MS Excel
Welcome to masteringexcel.in
Pre Test MS Excel Post Test MS Excel
Live Example
Click here to Download
Microsoft have created nice printable quick start guides for you. These printable materials can help new users getting oriented in the interface. or You can Download the PDF files for Excel, PowerPoint, Word, Outlook, OneNote, OneDrive, Teams, SharePoint, and Yammer. Download the files using the link below: Excel – Quick Start Guide PowerPoint- Quick Start […]
Steps to Populate Multiple Columns using Single Vlookup ——————————————————— 1) Select Columns or range of cells where we want to write formula. 2) Type Formula =VLOOKUP(A3,’Material Master’!A:E,{3,5,2},0) (AnchorData is name range of Master data) 3) Press Ctrl +Shift+ enter
Steps to Create Linier Series in Excel/WPS Spreadsheet———————————————————–1) Select any cell 2) Home Tab -> Rows & Columns -> Fill -> Series (WPS) / Home Tab -> Fill -> Series (MS Excel ) 3) Select Column Option -> Type Stop Value -> OK How to create date series Type first date manually -> Select the […]
Insert alternate blank rows using VBA code. Many time in MS Excel training program participants asked this Question. How to insert alternate blank rows. This is our input screen. After VBA Macros sheet will look like this. Open VBE (Visual Basic Editor ) Press Alt + F11 or ALT +Fn+F11 , Visual Basic Editor screen […]
I did lot of Advanced MS Excel corporate training workshop in Delhi, Mumbai, Chennai, Bangalore, Hyderabad pan India and abroad. One day i was doing training program in one of my customer in Bhilwara one of my participants asked me one question related to Pivot Table and on spot i develop on formula i want […]
VBA macros to combined file into master excel sheet. Click on the below link and run the program. VBA Macros is assigned on the command button. Download File
How to protect all worksheet using VBA Macros. Please do as follows: 1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window. 2. Click Insert > Module, and paste the following code in the Module Window. Sub protect_all_sheets() Dim pass As String Dim repass As String Dim i As Integer Dim s As Worksheet top: pass […]
If you want to unhide all the worksheet using vba code. Use following programs to unhide the sheet. Sub UnhideAllSheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub ————————————-Hide all worksheet except active sheet ——————————————— Sub HideAllExceptActive() Dim ws As Worksheet ‘ Loop through all the worksheets […]
Some time it is very difficult to move from one sheet to other sheet using short cut keys or manually. The following VBA code may help you to create indexes of sheet names. Please do as follows: 1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window. 2. Click Insert > Module, and paste the […]
It will help you automate formatting of conditional results in such a way which is color coded and easy to see and understand. Once defined, Excel will automatically change the color of your values e.g. up or down, high or low, pass or fail, profit or loss etc. Conditional Formatting (CF) is a tool that […]
Steps to combined multiple file using power query. To start the process of combining multiple files, first ensure that all the files you want to combine are contained in a single file folder. Click on the Data Tab -> From File -> From Folder Click on the browse button to select MS Excel files from […]
1.Navigating in Worksheets and Selecting Cells Ctrl+Shift+[+] Insert a new row or column (after the current row is selected with shift+ space, or column is selected with ctrl+ space arrow left, arrow right, arrow up,arrow down Move one cell up, down, left, or right in a worksheet. Ctrl+Arrow Keys Moves to the edge of the current data region Shift+Arrow […]
If-Then-Else-Nested If commands You can produce different set of results based on slabs, conditions, stages etc. It requires logical understating not any programming skills. What is the IF Function Excel’s IF function is one of simplest and most useful spreadsheet functions. It can fill cell fields for you based on evaluating a condition. Syntax IF(logical_test, […]
Vertical Lookup & Horizontal Lookup This is back of MS Excel formula, If you want to compare two datasheet, data table on basis of common column then we use vlookup & H lookup formula. Vlookup (short for ‘vertical’ lookup) is a built-in Excel function that is designed to work with data that is organised into columns. For a […]
Data formatting & custom settings Data formatting is inbuilt features which is applied on available data to for number, text, date, day, month, year, currency settings. Excel Formatting – number formatting Number formatting refers to the addition of currency symbols, decimal markers, percent signs, and other symbols that help to identify the type of data […]
Naveen Mishra is founder of msteringexcel.in Naveen Mishra is a well renowned Software Corporate Trainer. A highly focused and motivated Corporate Software Trainer with 16 Years of experience in IT industry having an extensive exposure as a Trainer, Project Manager, Software Developer, who thrives in successful delivery MS Project & technological trainings like Advanced MS […]