From time to time you need to rearrange data and clean it up in your spreadsheet
in order to display it the way that you want it in this video you'll learn how
to rearrange rows and columns how to spot and remove duplicates and how to
change data types for easier analysis let me show you. Here I have a
spreadsheet with customer prospects there we're going to share with an agency
for follow up I've been given another spreadsheet with additional prospects
that I need to add to my list but the data in this additional spreadsheet is
arranged differently and there might be duplicates so I need to clean up the
data before I can consolidate the lists the data in the second spreadsheet is
very compressed here is a row that only seems to contain hash tag symbols
this is Excel's way of telling you that the cell is too small to display the
full contents.
Hi I'm so sorry to interrupt I just wanted to let you know
that if you're annoyed by the ads in this video you can access our tutorials
ad free by getting a subscription to businessproductivity.com or signing
up for one of our many courses on platforms such as Udemy,
CyberU and Vimeo on demand I also wanted to take this opportunity to tell
you a bit more about Storyals. Storyals, which stands for story-based tutorials
is our video package offerings for organizations that want to increase
employee productivity using Office 365 with Storyals, organizations can inspire
motivate and educate users on effective use of Office 365 by showcasing
real-life best practices finally I want to encourage
you to subscribe to our YouTube channels Business Productivity and Storyals
here you will also find my video blog succeed in the digital workplace as well
as other videos that can help you increase your productivity if you have
any questions or comments please post them here and I'll do my very best to
get back to you with that let's go back to the tutorial thank you for watching.
You can make the column wider by dragging the column edge to the right to
see the full contents instead of resizing individual columns you can
resize all columns and rows at the same time to change the width of all columns
mark the entire spreadsheet and then make any column wider all columns will
then have the same width you can also double click between two columns to
automatically make the columns as wide as they need to be to properly show the
contents you can do the same for the rows by double clicking between two rows
as you can see the entire spreadsheet is now much easier to read now I can see
that the layout of this data is different than in my original list to
pen this list mine I first need to convert the rows into columns that is
transpose the data to transpose data first mark the entire data range by
marking the first cell and then pressing the keyboard shortcut control shift end
right click and select copy I'll open up a new worksheet by clicking the plus
sign at the bottom of the spreadsheet to rearrange the data from row two column
layout right-click and under paste options select transpose the data is
copied and turned from column to row again to change the width of the columns
I'll mark the spreadsheet and double click before I copy data to the other
spreadsheet I need to make sure that the spreadsheets contain the same
columns and that they are in the same order to compare the spreadsheets
side-by-side I'll press the keyboard shortcut windows key arrow right to move
this window to the right then I'll mark my other spreadsheet instead of using
the keyboard shortcut I can use the mouse and drag the window to the left
side of the screen here I can see that my initial list doesn't contain the
column named system number my original list also has the ID number as the very
first column in my original spreadsheet the contents of the joined date column
is stored as dates this means that I can easily filter the list by joint date in
the second spreadsheet the data is stored as text if I go to the data tab
and select filter and look at the joined date column you can see that I just get
a long list of numbers Excel doesn't treat the data here as dates I'll cancel
here and remove the filter from my list to make the necessary fixes to the
spreadsheet I'll maximize the window again by
clicking maximize in the top right corner to delete a column right-click
the column heading letter and select delete to move a column mark the column
by left clicking the column letter move your mouse cursor to either side of the
column until you see a cross hold down the right mouse button and drag the
column to the desired position release the mouse button
and from the menu select shift right and move the columns are shifted to the
right to make room for the ID number column to convert the data type of the
joined date column from text to date mark all cells that contain the text
string by marking the first cell and then pressing control shift arrow down
on your keyboard click the data tab and then in the data tool section click text
to columns a wizard opens up that steps you through
the process in the first step you're asked about delimiters you don't have to
change anything here so click it next to go to the next step in the second step
uncheck all the limiters since the texturing is all in a single string in
the third step of the wizard you have the option to set the data format select
date specify the format as year month and day since this is the way the text
string is written and then click finish as you can see the format of the join
date column has not been changed and if I click on the filter button I can
select to filter this column on a specific year or month now I can copy
the data over to my original spreadsheet before I do let's save the changes to my
spreadsheet to copy the full data range I'll mark the first cell under the
heading row and press control-shift end on my keyboard I'll right-click and
select copy next I'll open up my original spreadsheet and go to the very
end by pressing control end I'll mark the first empty cell under my list
right-click and select paste to go back to the top again I'll press ctrl home
now I have all data consolidated in one spreadsheet now I just have to make sure
that I don't have any duplicates to easier spot duplicates start by sorting
your data on unique values in this list two people might have the same first the
last name but the ID number for everyone is unique to sort on the ID number
column I'll mark a cell in this column click the data tab and then sort A to Z
I can immediately see from just looking at the data that I do have duplicates to
make duplicates stand out even more you can highlight them using conditional
formatting mark the column where you want to highlight duplicates in my case
it's the first column with the ID number on the Home tab click conditional
formatting and select highlight cells rules and then duplicate values here you
can select how you want the duplicates to be formatted eldest leave the default
coloring which is light red fill with a dark red text I'll click OK and now the
duplicates in my dataset are clearly visible if I scroll down I can see that
I have even more duplicates further down on the list to see all duplicates at the
top you can sort by color click the data tab and in the sort and filter section
click the sort button instead of sorting the ID number column by values I'll
change the sort on drop-down to cell color in the order drop-down I'll select
the red color and then leave the default which is on top now all duplicate
records are sorted on top I'll mark all colored cells and can see in the bottom
of a spreadsheet that the cell count is 46 which means I have 23 duplicate
records instead of manually marking and deleting the duplicates I can use the
built-in tool in Excel to automatically remove duplicates mark any cell in your
data set on the data tab in the data tool section click remove duplicates
here I'll leave the checkbox that says my data has headers marked leave all the
columns checked to only remove rows for the values in all columns are the same
and then click OK as you can see Excel removed 23 duplicate records now I'll
save this as a new consolidated list I'll click file save as and then save my
spreadsheet by knowing how to rearrange and clean up your data you'll save a lot
of time when working on large data sets
For more infomation >> 'Small Business Saturday': How local businesses keep up with online shopping - Duration: 2:13. 
For more infomation >> Pop-Up Bakery - Duration: 4:15. 
For more infomation >> Sacramento Stores Team Up For Small Business Saturday Shopping Tour - Duration: 1:48. 
For more infomation >> Volkswagen up! 1.0 move up! BlueMotion (60pk) 5-drs/ Navi!/ Airco/ Elek. Ramen/ C.V. Afstand/ Isofix - Duration: 0:50. 
For more infomation >> Volkswagen up! 1.0 60PK High up! - Duration: 0:54. 
Không có nhận xét nào:
Đăng nhận xét