Excel - Combining Tricks to Manipulate Data
There are so many tricks in Excel to help you. Over the past two years I have shown you these tips one at a time. But if you combine these tricks you can manipulate data to make it the way you need it.
Let’s use an example of something I do monthly to create a list of birthdays for use in an organization’s newsletter. The list comes to me in PDF format (tip - with PDF’s you can usually copy the text and transfer to other programs). I perform the following steps:
1. Highlight and copy the dates and name from the list (see picture below – left side). Then, it is pasted (not a paste special) it in Excel http://www.aapk.com/amys_biz_tips_041912.html. This list now has the date, names, and phone numbers for each person all on one line. I need each item separated into columns and the phone numbers removed.
2. To get the data into separate columns – highlight the data and Choose Data, Text to Columns (http://www.aapk.com/amys_biz_tips_112411.html) and break the data up by space.
3. Now commas need to be removed from after the last name so I would highlight the column with just the last names and use Find/Replace (“,” is the find and replace is a space or “ “).
4. At this point I sort the list so that the birthdays and last names are in ascending order (1-31, A-Z). http://www.aapk.com/amys_biz_tips_110311.html
5. To make the list read correctly, I need to combine the first name (in column B) and last name (in column A) into one text column . To do this I create the following formula: = B1&” “&A1 (http://www.aapk.com/amys_biz_tips_030812.html) and copy it down the whole list giving me a first and last name
6. This list of names needs to be changed from a formula into a value, so that when it is moved, the text holds. To do this I highlight the column with the formulas and hit copy. Then in the same column I use Paste Special, Values, to replace the formulas with the text values (http://www.aapk.com/amys_biz_tips_041912.html)
7. Next is reviewing the list and cleaning up any errand data, copying it, and pasting into the program I use to create the bulletin.
This is a just one example of using Excel to change data into the format that works for you. There are many other ways to combine the tricks I have written about. I recommend before playing with the data you write down a list of what steps you need to take and then work your way through them. You may have use Ctrl-Z to “undo” a step or two. So if you don’t succeed the first time, keep trying them out until you get the right combination.
If you need help working with an MS office project contact me at firstname.lastname@example.org I have years of experience and can help you.
Trends in Measuring Social Media
Over the past four years, as social media has become a larger part of every company’s marketing toolbox, there have been shifts in what is measured to determine the success of campaigns. These shift are different than any other marketing vehicle in the past because before marketers would measure success based on sales.
Based on recent research, marketers have moved from the intangible goals of things like brand awareness, message effectiveness and brand perception to page hits and conversation engagement (which can include followers, likes, etc).
This is a shift that I am happy to see, because a few years ago I thought, how in the world are companies going to find a measured connection between sales and visitors unless they were running an ecommerce site. Now by concentrating on engaging people marketers can be more effective in seeing how their marketing efforts drive customers’ opinions, buying habits and recommendations to friends. This was never possible in the old days of marketing.
The folks at Social Media Examiner have put together a list of three trends that have shifted in measurements. See the full article here at: http://www.socialmediaexaminer.com/research-shows-metrics-marketers-think-matter/
If you need help with your social marketing contact me at email@example.com, I have years of experience.
You Have To Laugh
Cat Owners’ Struggles ... http://justsomething.co/hilarious-cat-owners-struggles/