Get Data: MS-Excel 2016 includes a powerful new set of features called Get Data, which provides fast, easy data gathering and shaping capabilities. Get Data enables you to connect, combine, and refine data sources to meet your analysis needs. While some data analysis efforts focus on a few of these steps, each step is important in the data analysis and transformation process. Are you using a previous version of Excel? If so, take a look at Power Query - Overview and Learning for an overview based on previous versions of Excel with the Power Query Add-in. The content and premises are similar, but some screens and steps are slightly different. Note: There are different pathway for each source to import data to active sheet, please follow the right pathway to do it correctly. How to get transform the data to active workbook from a file/workbook that I have saved under name of “chicago” in the “Desktop”? Select Data Menu => Click Get Data Tool => Select From File => Click From Workbook => Select location “Desktop” => Select file name “chicago” => Click Import button => Select sheet name “Sheet1” => Click Load button (Now, you can see the data has got transferred at new worksheet in your workbook). For more understanding, look at the screenshot please: Data Validation: In MS-Excel 2016, data validation is a feature that is used to control what a user can enter into a cell. For example, you could use data validation to make sure a value is a number between 0 and 8, make sure a date occurs in the next 30 days, or make sure a text entry is less than 20 characters. Data validation can simply display a message to a user telling them what is allowed. Data validation can also stop invalid user input. For example, if a product code fails validation, you can display a message like what you want to say. In addition, data validation can be used to present the user with a predefined choice in a dropdown menu. This can be a convenient way to give a user exactly the values that meet requirements. And data validation is implemented via rules defined in Excel's user interface on the Data tab of the ribbon. How to create a validation to enter a value is a number between 1 to 100 into both columns called Maths and Science in the students marksheet? Select cells that you want to create validation => Select Data Menu => Click Validation Tool => Select “Whole number” in the Allow box => Select “Between” in the data box and then type min “1” and max “100” in both boxes => Select Input Message tab => Type input message title “Validated” and then type message for display “Please Enter only values 1 to 100” => Select “Error Alert” tab => Select Stop in the style box and then type title “Error” and message “Please follow up validated numbers 1 to 100” => Click OK. For more understanding, look at the screenshot please:
0 Comments
|
Archives |