Formatting and handling Data
Excel isn’t just another Notepad when it comes to styling or precisely formatting the data. It has all the basic formatting tools you find in word processor. However, Excel advances in formatting zone with such features that are not available with other applications in Office suite. And most importantly some can do much more than just changing the appearance of data.
Things to learn in home tab
Clipboard: With few ordinary ones and couple of extraordinary ones. Cut, copy paste is not new to us I assume and they perform the same basic function just like other applications. However if you click the drop-down arrow buttons then you will find few extra bits worthy of learning.
Copy as picture: this will copy the data as a snapshot of that place which you can literally size and edit just like any other picture.
Paste special: this command can do much more than simple paste like pasting just the values or transpose the content.
Format painter: If you have a cell that is formatted to desire and you want the same formatting to be applied on another cell then simply turn the it on and click over the target cell and it will apply all the format settings in an instant.
TUTORIALS SPECIFIC TO HOME TAB
Font: In this group you can choose to change font family, styling of text, size, apply or change borders and change color of cell and font.
Alignment: In this group you have commands that let you change the text or content alignment in cell. You can also change the orientation i.e. you can have your text at a certain degree of angle instead of simple horizontal.
Wrap text: As you can change the width of columns at your will, you may have a text that is longer than the width. Wrap text, as the name suggest, will wrap the text by inserting line breaks so that your content can fit in the same width.
Merge & Center: It will merge multiple cells in one and make the content center aligned.
Number: One of my favourite group to work. No analysis will ever be complete without numbers. Excel gives us ample ways to format numbers as well. You can format numbers in the form of currency, telephone number, zip-code, date, percentage and above that ability to make your own formatting style.
TUTORIALS ON NUMBER FORMATTING
Dealing with Telephone numbers in Excel – Dialing in the correct format
Using Excel Custom Number Formatting as Data Validation Tool
Formatting Dates in Excel the better way using Custom Number Formatting – How To
Styles: This group has options that help you give meaning to your data with style.
Conditional formatting: help you style content in a particular fashion if they fulfill a certain condition. Its a powerful tool and one can employ it to great extent.
Format as table: turn the range in a table. I will discuss about tables and their abilities in more details later. For now check out following tutorials to make yourself aware of the amazing things they can pull off:
Cell styles: ready made styles available for you to be applied on a click of a button and saving you time.
TUTORIALS ON TABLES / CONDITIONAL FORMATTING
20+ Excel Table tricks to turbo charge your data
Absolute or Static structured references in Excel table – How to
Comparing two columns to highlight Duplicates in the same row – Excel Conditional Formatting
Cells: want to add more rows or columns before or after your active cell? This is the group you must access. You have options to insert/delete rows, columns and even worksheets. You can even hide/unhide a worksheet, row and column. And not to forget you can change the color of worksheet tab and even lock the worksheet to prohibit editing or even opening.
Editing: Last group in home tab provides you with basic editing functions like:
Find and select: find the desired result AND select it if you desire.
Sort and filter: arrange the data in descending or ascending order
Clear: clear all the applied format settings or only specific ones
TUTORIALS ON DATA EDITING
One Minute Excel Tutorial – Sorting
Multi level or Multi column sort in Excel – How To
How to sort Rows horizontally left to right instead of Columns vertically top to bottom in Excel
Selecting and Highlighting difference using Excel Go To Special Row / Column Difference
Quickly fill Blank cells with the Value in the Cell Above
Custom Autofill series and Custom sorting with Custom lists in Excel
Automating Data Entry using Data Validation in Excel