How to clear formatting in excel and leave the content

#HOW TO CLEAR FORMATTING IN EXCEL AND LEAVE THE CONTENT UPDATE#

One of the first things I did to the spreadsheetВ was toВ convert it to a table so I couldВ sort and filter the columns by variables. (For those of you thinking вЂњThatвЂ™s not a very big list,вЂќВ the list is incomplete вЂ“ itВ only contains dataВ from the dateВ we started using that specific SharePoint list). I now have ONE spreadsheet with 30 columns andВ 309 rows (which is growing daily as I continue to add more data) that manages aВ list of active and inactive clients. The best part is, Darren or IВ can go in the Master file toВ update the information, thenВ create a copy as a separate file toВ slice and dice the data to getВ whatВ we need toВ do a mail merge, then toss that copy when weвЂ™re done without the need to store duplicates. ThatвЂ™s whyВ I created a Master Client List in Excel (stored in a SharePoint library) that keeps track of all Client information that is not related to Billing or Time Entries.

Part of the decision-making process would involveВ my input on what type of data I needed to do my job.At the end of the day, the Dev Team would be happiest with a file containing clean data that could be imported into our system of choice when that decision was made by Leadership Team.I needed to have access toВ an active, accurate record of this information, in the meantime. (If you want to know why, take a gander at this: вЂњWhy IвЂ™m Using an Excel 2016 Spreadsheet to Manage DataвЂњ). Now was not the time to reinvent the wheel or build something new in SharePoint.Which is not a huge deal, until you start addingВ up the time toВ update all of these when a new client started working with us, and time others spend searching for where I stored these lists.) (At that point in time, we hadВ a workbook to track Christmas Cards,В anotherВ workbook to track Event invitations, a OneNote table to track invitees to our Office 365 Lunch and Learns, another table to track New Client Suite Night invites, etc. My immediate thought was: вЂњIf we hadВ ONE list to rule them all,В that tracked ALL the information in one spot, IвЂ™d only have toВ keep one location upВ to date.вЂќ And, if a new client did not receive a card because of a data entry error, IвЂ™d feel like Ebenezer Scrooge.

Even with some of the tools available in Excel to assist with this task, at the end of the day I donвЂ™t trust robots to do a humanвЂ™s job. Alas, I had not, and now I faced merging data. If I had added the column in the SharePoint list, IвЂ™d be able to export a fresh list with two mouse clicks.

I struggle to recall why I decided to store data in a separateВ Excel spreadsheet. I had produced a quick result, that made more work for my future self. (ArenвЂ™t I a gem?) To be clear, this reaction was NOT directed at DarrenвЂ™s request, but at myself for not working smart, andВ beingВ inefficient.

#HOW TO CLEAR FORMATTING IN EXCEL AND LEAVE THE CONTENT UPDATE#

When I realized the amount of workВ that wouldВ need to be doneВ to update and validate the data to make sure it was clean, free of duplicates, and not missing anyone, this was my immediate reaction: Client Address (ok, this was more like 6 columns, but I want this blog to be #KISS)ДЄfter exporting the information, I savedВ the fileВ to our MarketingВ SharePoint library and added a column in the Excel worksheetВ for вЂњReceive Card?вЂќ with three choices: вЂњYes, No, To Be Determined.вЂќ.The original worksheet was created using a simple Export to Excel from a SharePoint list withВ threeВ columns visible: The idea was we could reuseВ the 2015 listВ forВ our 2016 mail-out,В after adding the 30+ new clients we started working with this year. Our General Manager, Darren,В asked me to track downВ theВ Excel workbookВ we used last year toВ determine which clientsВ receive Christmas Cards and thenВ create aВ mail mergeВ to printВ labels with their addressesВ to use on theВ card envelopes. HereвЂ™s some back story to give it context: When using Excel today,В I hit a roadblock and found a solution that I want to share.

How to clear formatting in excel and leave the content