Merging cells in excel and delete the data

If youвЂ™re wondering why you may want to unmerge cells, let me give you some reasons. To select all the cells at one go, use the keyboard shortcut Control + A + A The Problem with Merged Cells (And Why you may need to Unmerge Cells) In case you have a lot of merged cells in the worksheet and you want to quickly unmerge all the cells at one go, select all the cells in the worksheet and then use any of the methods shown above. So there are two quick ways to unmerge cells in Google Sheets.

In the options that appear, click on the вЂUnmergeвЂ™ option.

Click on the small triangle at the right of it

In the toolbar, there is a вЂMerge cellsвЂ™ icon.

There is an option in the toolbar in Google Sheets that also has the option to unmerge.Д«elow are the steps to unmerge cells using the toolbar in Google Sheets: There is another (faster) way to unmerge cells in Google Sheets. Note that the unmerge option appears only when you have selected at least one merged range. The above steps would instantly unmerge the selected cells.

Hover the cursor over the Merge Cells option.

Select the range that you want to unmerge.

Suppose you have the dataset as shown below and you want to unmerge a specific range of cells (B1:D1) The process is quite similar to what you do when you have to merge cells. You can use the pivot table calculate page view totals for site sections and dates.Merging cells is quite common when youвЂ™re working with data in Google Sheets.ДЄ lot of times, people merge cells while creating headers or labels.ДЄnd you can also easily unmerge cells in Google Sheets. Now youвЂ™ll have two new columns, which you can title Site Section and Date, and two old columns Page and Page Views.ДЇollowing these steps lets you split off data from the original URL and add it to the spreadsheet in such a way that lets me create a pivot table.

Delete the Month, Day, and Year columns.

Highlight this column with the new dates, and copy, then paste values in the same column.

Drag this formula down to fill the cells in the column. Notice that I added вЂњ/вЂќ between the month/day/year.

To the right of the three new columns (month, day, and year), enter the Concatenate function in the first blank cell.

This lets you create a pivot table that pivots by the complete date.

Next youвЂ™ll recombine the date into one cell (month/day/year) by using the Concatenate function.

Delete the other new columns that the W izard just created.

The site section names and the dates are now split into three columns.

(In my example URLs, the slash / separates the parts of the URL, so you can use it to split off the data.)

In the box next to Other, enter aВ slash /, and then click Finish.

In the next page of the Wizard, uncheck Tab and chose Other.

In the Wizard, choose Delimited, and click Next.

Highlight this new column, click the Data tab, and in the Data Tools, click Convert Text to Columns wizard.

Select and copy the column with the URLs, then paste into a new column to the right of existing columns.

Since the URLs have slashes / between each of its parts, it will be easy use the Convert Text to Columns wizard ( Wizard) to split off the section name and publication date, and turn them into new columns In the sample URLs below, I want to split off the site sections вЂњcontactusвЂќ and вЂњhome.вЂќ And I want to split off the date that the pages were published вЂњвЂќ and вЂњвЂќ. Trick: Use the ConvertВ Text to Columns wizard and the Concatenate function to split and merge data

These steps can also be applied to other situations when you need to split and merge data.

Once you learn the steps, it will take you only a few minutes to complete the tasks. Instead, I plan on using the Convert Text to Columns wizardВ and Concatenate function. To save time, I want to avoid manually entering this data in new columns. IвЂ™d like to calculate theВ total number of page views by site section and the total number of page views by publication date. The URLs include a name for a section of the site and the date when the page was published. I have a list of URLs in one column and their corresponding page views in another. ) Issue: Inefficient way to calculate page views based on data points in URLs Check out her first trick Resizing column widths in pivot tables. (Who could be better than a Business Intelligence analyst at teaching us a thing or two about Excel? We asked our own number-crunching wizard Stacey Armstrong to share some Excel tricks sheвЂ™s learned along the way.

Merging cells in excel and delete the data