Our General Manager, Darren,\u00a0asked me to track down\u00a0the\u00a0Excel workbook\u00a0we used last year to\u00a0determine which clients\u00a0receive Christmas Cards and then\u00a0create a\u00a0mail merge\u00a0to print\u00a0labels with their addresses\u00a0to use on the\u00a0card envelopes. The idea was we could reuse\u00a0the 2015 list\u00a0for\u00a0our 2016 mail-out,\u00a0after adding the 30+ new clients we started working with this year.\u00a0 The original worksheet was created using a simple Export to Excel from a SharePoint list with\u00a0three\u00a0columns visible:<\/p>\n
- \n
- Client Name<\/li>\n
- Key Contact Name<\/li>\n
- Client Address (ok, this was more like 6 columns, but I want this blog to be #KISS)<\/li>\n<\/ol>\n
After exporting the information, I saved\u00a0the file\u00a0to our Marketing\u00a0SharePoint library and added a column in the Excel worksheet\u00a0for “Receive Card?” with three choices: “Yes, No, To Be Determined.”<\/p>\n
When I realized the amount of work\u00a0that would\u00a0need to be done\u00a0to update and validate the data to make sure it was clean, free of duplicates, and not missing anyone, this was my immediate reaction:<\/p>\n
![\"johnfrancisdaley_ughface\"](\"https:\/\/itgroove.net\/oh365eh\/wp-content\/uploads\/sites\/12\/2016\/12\/JohnFrancisDaley_UghFace-300x226.jpg\")
<\/a><\/p>\n(Aren’t I a gem?)<\/em> To be clear, this reaction was NOT directed at Darren’s request, but at myself for not working smart, and\u00a0being\u00a0inefficient. I had produced a quick result, that made more work for my future self. Boo!<\/p>\n
I struggle to recall why I decided to store data in a separate\u00a0Excel spreadsheet. If I had added the column in the SharePoint list, I’d be able to export a fresh list with two mouse clicks. Alas, I had not, and now I faced merging data. 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. <\/em>And, if a new client did not receive a card because of a data entry error, I’d feel like Ebenezer Scrooge.<\/p>\n
My immediate thought was: “If we had\u00a0ONE list to rule them all,\u00a0that tracked ALL the information in one spot, I’d only have to\u00a0keep one location up\u00a0to date.”<\/p>\n
(At that point in time, we had\u00a0a workbook to track Christmas Cards,\u00a0another\u00a0workbook 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. Which is not a huge deal, until you start adding\u00a0up the time to\u00a0update all of these when a new client started working with us, and time others spend searching for where I stored these lists.)<\/em><\/p>\n
I knew\u00a0four things:<\/p>\n
- \n
- Now was not the time to reinvent the wheel or build something new in SharePoint. (If you want to know why, take a gander at this: “Why I\u2019m Using an Excel 2016 Spreadsheet to Manage Data<\/a>“).\u00a0<\/em><\/li>\n
- I could not wait for the new system. I needed to have access to\u00a0an active, accurate record of this information, in the meantime.<\/li>\n
- 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.<\/li>\n
- Part of the decision-making process would involve\u00a0my input on what type of data I needed to do my job.<\/li>\n<\/ol>\n
That’s why\u00a0I 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. The best part is, Darren or I\u00a0can go in the Master file to\u00a0update the information, then\u00a0create a copy as a separate file to\u00a0slice and dice the data to get\u00a0what\u00a0we need to\u00a0do a mail merge, then toss that copy when we’re done without the need to store duplicates.<\/p>\n
The Front Story:<\/strong><\/p>\n
I now have ONE spreadsheet with 30 columns and\u00a0309 rows (which is growing daily as I continue to add more data) <\/em>that manages a\u00a0list of active and inactive clients. (For those of you thinking “That’s not a very big list,”\u00a0the list is incomplete – it\u00a0only contains data\u00a0from the date\u00a0we started using that specific SharePoint list).<\/em><\/p>\n
One of the first things I did to the spreadsheet\u00a0was to\u00a0convert it to a table so I could\u00a0sort and filter the columns by variables. I quickly learned how moving column order was a pain, and how adding new rows or columns in the middle of the table ruined the formatting. I wanted to remove the table formatting without ruining my data…The\u00a0main issue was this:\u00a0 I didn’t know\u00a0what a\u00a0“range” or a “normal range of cells” meant so I\u00a0completely overlooked the “Convert to Range” button.<\/p>\n
If you aren’t familiar with what table looks like in Excel, here’s a visual:<\/p>\n
![\"excel-2016-table-vs-range\"](\"https:\/\/itgroove.net\/oh365eh\/wp-content\/uploads\/sites\/12\/2016\/12\/Excel-2016-Table-vs-Range.png\")
<\/a><\/p>\nThe top image is a Table. You can tell because Row 1 is a header with dropdown menu buttons that allow you to sort and filter the data. Another way to tell is if you scroll down your\u00a0worksheet – the Table headers will always be visible. The bottom image is a “normal range of cells”. You can tell because Row 1 is not a header and does not have dropdown menu buttons. As there are no headers, the headers do not follow you as you scroll down.<\/p>\n
So began my journey to figure out how to remove table formatting in Excel 2016… which involved a lot of clicking around and Google searching. I found the answer on the Mr. Excel.com Forum<\/a>.<\/p>\n
Here’s the steps:<\/div>\n- \n
- Use your cursor to select one cell within the table to make the Table Tools Tab will appear above the Design Tab.\n
- \n
- The Design Tab is only visible when a portion of your Worksheet has been formatted as a table. <\/em><\/li>\n
- Until you select a cell in the table, 75% of the buttons in the Design tab will stay grayed out.<\/em><\/li>\n
- The keyboard shortcut for Select all (Ctrl + A) works as well – because pressing Ctrl + A once only selects the current region first, and you have to press Ctrl + A a second time selects the entire worksheet.<\/em><\/li>\n
- That is why using the “Select All” button does not work, as it automatically selects all the cells in a worksheet.<\/em><\/li>\n<\/ol>\n<\/li>\n
- Select the Design tab.<\/li>\n
- In the Design tab, click on Convert to Range.<\/li>\n
- Press Yes on the “Do you want to convert the table to a normal range?” pop up appears.<\/li>\n<\/ol>\n
![\"excel-2016-table-convert-to-range\"](\"https:\/\/itgroove.net\/oh365eh\/wp-content\/uploads\/sites\/12\/2016\/12\/Excel-2016-Table-Convert-to-Range.png\")
<\/a><\/p>\nYour table has now been converted to a “normal” range of cells.<\/p>\n
You can then use the Select All button followed by font formatting buttons to set the Fill Colour to No Colour.<\/p>\n
- Until you select a cell in the table, 75% of the buttons in the Design tab will stay grayed out.<\/em><\/li>\n
- The Design Tab is only visible when a portion of your Worksheet has been formatted as a table. <\/em><\/li>\n
- Now was not the time to reinvent the wheel or build something new in SharePoint. (If you want to know why, take a gander at this: “Why I\u2019m Using an Excel 2016 Spreadsheet to Manage Data<\/a>“).\u00a0<\/em><\/li>\n
![\"johnfrancisdaley_ughface\"](\"https:\/\/itgroove.net\/oh365eh\/wp-content\/uploads\/sites\/12\/2016\/12\/JohnFrancisDaley_UghFace.jpg\")
<\/a><\/p>\n