Protect rows from getting deleted

Instructions on the worksheet ask people not to delete any rows. Someone will try to delete a row. Off to the right, select Z1:Z8. Type =2 and press Ctrl+Shift+Enter. This creates an array in those rows.

If someone tries to delete a row, Excel prevents it and shows a cryptic message about arrays, shown below.

When someone tries to delete a row in the area of rows 1 to 8, a message will appear with "You Can't Change Part Of an Array."

Comments

Data Cleaning Functions in Excel

The CLEAN function Using the CLEAN function removes nonprintable characters text. For example, if the text labels shown in a column are using crazy nonprintable characters that end up showing as solid blocks or goofy symbols, you can use the CLEAN function to clean up this text. The cleaned‐up text can be stored in another column. You can then work with the cleaned text column. The CLEAN function uses the following syntax: CLEAN(text) The text argument is the text string or a reference to the cell holding the text string that you want to clean. For example, to clean the text stored in Cell A1, use the following syntax: CLEAN(A1) The CONCATENATE function The CONCATENATE function combines, or joins, chunks of text into a single text string. The CONCATENATE function uses the following syntax: CONCATENATE(text1,text2,text3,...) The text1, text2, text3, and so on arguments are the chunks of text that you want to combine into a single string. For example, if the city, state, and ZIP co...

Import CSV In Power BI

Import CSV file Click on Get Data à More à File option and select Text/CSV . Navigate to the CSV file which needs to be imported FL_insurance_sample.csv . Select the file and click on Open. FL_insurance_sampleDownload In the CSV window on top we have 3 dropdowns, preview of data and data load options. Select Load and it will load to power query editor window. In the Power query editor the CSV file is loaded as a Queries . In power query editor we can edit, clean and transform the file as required. 3 Dropdowns and Data Load File Origin – Type of file origin. By default its 1252 Wester European (Windows). It’s the file type as per OS and region and country. Delimiter – Delimiter for column separation. By default it detects the delimiter from data , If the delimiter is not from the default options then we can select custom delimiter. Data Type Detection – By default it detects data types of columns based on top 200 rows, we can select entire data or do not detect data type ...

Formatting In Excel - helps you find meaning in the spreadsheet

Formatting In Excel - helps you find meaning in the spreadsheet Spreadsheets are often seen as boring and pure tools of utility, but that doesn't mean that we can't bring some style and formatting to our spreadsheets Formatting helps your user find meaning in the spreadsheet without going through each and every individual cell. Cells with formatting will draw the viewer's attention to the important cells. In Excel, formatting worksheet data is easy. You can use several fast and simple ways to create professional-looking worksheets that display your data effectively. For example, you can use document themes for a uniform look throughout all of your Excel spreadsheets, styles to apply predefined formats, and other manual formatting features to highlight important data. Formatting a Data Raw Data Using Font, Number tabs as shown in image to do a simple formatting Formatting a Data with help of formatting tools in Excel As shown in image , we have Prod...

Solutions for Data Science

Search This Blog