Select if you want to go with \u2018delimited\u2019 or \u2018fixed width\u2019<\/li>\n<\/ul>\nDelimited<\/em>: Breaking up the cell into columns based on special characters like space, tab, @, commas, etc.<\/p>\nFixed Width<\/em>: Gives you the flexibility to split each cell at a different location. This option will ask for the location of split for every cell in the column.<\/p>\n\n- Adding more than 1 row or column<\/strong><\/li>\n<\/ol>\n
As we start working on Excel, we realize the need to add more rows and columns, sometimes this addition can reach to hundreds. To avoid adding one by one, there is a simple way to do the same:<\/p>\n
\n- Select the exact number of columns or rows you want to add in the sheet.<\/li>\n
- And then right click and select \u2018insert\u2019.<\/li>\n<\/ul>\n
\n- Conditional Formatting formula<\/strong><\/li>\n<\/ol>\n
Conditional formatting formula allows you to change the color of the relevant cells on the basis of information in the cell. For instance, if you want to flag the numbers that are higher than 50%, that can be done.<\/p>\n
\n- Select the group of cells where you want to do conditional formatting.<\/li>\n
- Then, choose \u201cConditional Formatting\u201d from the Home menu and select the logic from the drop-down. This will help you either select the pre-existing conditions or make your own rule.<\/li>\n
- Select \u2018ok\u2019 when done and the results will appear.<\/li>\n<\/ul>\n
\n- If statement<\/strong><\/li>\n<\/ol>\n
If the statement is a tool that helps you to insert value in a cell based on existing data if the statement is true or false. For instance, if you have a supply list of 1000 items and you want to write high, low and medium in front of them based on their quantities then you can use If statement:<\/p>\n
\n- =IF(logical_test, value_if_true, value of false)<\/strong><\/li>\n<\/ul>\n
Eg: =IF (size>1000, \u201chigh\u201d,\u201d0\u201d)<\/p>\n
\n- Hence, if the size of the consignment is less than 1000, the value that will appear in the cell will be \u201c0\u201d else \u201chigh\u201d.<\/li>\n<\/ul>\n
\n- Dollar Sign<\/strong><\/li>\n<\/ol>\n
When you copy a relative formula<\/em> from one cell to another, it\u2019ll adjust the values in the formula based on where it\u2019s moved. But sometimes, we want those values to stay the same no matter whether they\u2019re moved around or not \u2013 and we can do that by making the formula in the cell into what\u2019s called an absolute formula<\/em>.<\/p>\nTo change the relative formula<\/em> (=A5+C5) into an absolute formula<\/em>, we\u2019d precede the row and column values by dollar signs, like this: (=$A$5+$C$5).<\/p>\n\n- VLookup<\/strong><\/li>\n<\/ol>\n
What to do if you have two sheets to integrate, for instance, if you have names and email ID in Sheet #1 and address and contact number corresponding to email ID in another i.e Sheet #2? VLookup can help you with this:<\/p>\n
\n- =VLOOKUP(lookup value, table array, column number, [range lookup])<\/strong><\/li>\n
- Lookup Value: The value of the email ID corresponding to which you want to insert the address and contact number.<\/li>\n
- Select the table array from the sheet #2.<\/li>\n
- Now select the value of the column number from the selected array, for instance, if the first field to be inserted is the address, which will be in the second or third column of the sheet #2. Insert 2 or 3 in the formula.<\/li>\n
- The range lookup is just to ensure that one pulls only exact value match as a result.<\/li>\n<\/ul>\n
\n- Pivot Tables<\/strong><\/li>\n<\/ol>\n
Pivot tables are very helpful if you want to summarize a heavy and long data. For instance, from the 1000 set supply data, you want to figure out the number of the consignment that came from Delhi, you can use Pivot tables.<\/p>\n
To create the Pivot Table, go to Data and select Pivot Table. Excel will automatically populate your Pivot Table, but you can always change around the order of the data. Then, you have four options to choose from.<\/p>\n
\n- Report Filter:<\/strong> This allows you to only look at certain rows in your dataset. For example, if you want to create a filter for hosiery consignment, you can select the data set with the same.<\/li>\n
- Column Labels:<\/strong> These could be your headers in the dataset.<\/li>\n
- Row Labels:<\/strong> These could be your rows in the dataset. Both Row and Column labels can contain data from your columns (e.g. Name of the consignment can be dragged to either the Row or Column label \u2014 it just depends on how you want to see the data.)<\/li>\n
- Value:<\/strong> This section allows you to look at your data differently. Instead of just pulling in any numeric value, you can sum, count, average, max, min, count numbers, or do a few other manipulations with your data. In fact, by default,<\/em> when you drag a field to Value, it always does a count.<\/li>\n<\/ul>\n
\n- Summarize data with CountIf and SumIf function<\/strong><\/li>\n<\/ol>\n
Another great tool is \u201cCountIf\u201d and \u201cSumIf\u201d.<\/p>\n
\n- CountIf: <\/strong>What if you want to count the number of times, silk consignment is mentioned in the data set? You can use CountIf function to do the same<\/li>\n<\/ul>\n
=COUNTIF (range, criteria)<\/strong><\/p>\nHere the criteria will be \u201csilk\u201d<\/p>\n
\n- SumIf: <\/strong>And if you want to sum of silk consignments, you can use this formula.<\/li>\n<\/ul>\n
=<\/em>SUMIF(range,criteria,sum range)<\/strong><\/p>\nHere the criteria will be \u201csilk\u201d and sum range will the \u201cquantity of the consignment\u201d.<\/p>\n
\n- Combining cells using \u2018&\u2019<\/strong><\/li>\n<\/ol>\n
Databases tend to split out data to make it as exact as possible. For example, instead of having a data that shows a person\u2019s full name, a database might have the data as a first name and then the last name in separate columns. In Excel, you can combine cells with different data into one cell by using the \u201c&\u201d sign in your function.<\/p>\n
The formula with variables: =A2&\u201d \u201c&B2<\/strong><\/p>\n\n- Using shortcuts to quickly format values<\/strong><\/li>\n<\/ol>\n
For a number with two decimal points, use <\/strong>Ctrl + Shift + !<\/strong>. For dollar (converting data into Dollar currency) use Ctrl + Shift + $<\/strong>. For percentages it\u2019s Ctrl + Shift + %<\/strong>. For converting data into time use Ctrl+Shift+:<\/strong>. There are many more such hacks, and you can find them easily on the internet.<\/p>\nKredX is an Invoice discounting marketplace helping Businesses address cash flow issues, to learn more visit www.kredx.com<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"Ever used Excel? Well, most of you would say \u2018Yes\u2019 to this question. Excel is an important tool for every kind of business and it completes every requirement. If you are ever in a situation, where you are doing a lot of manual work in the excel sheet then be sure that you are missing […]<\/p>\n","protected":false},"author":1,"featured_media":5891,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35],"tags":[],"class_list":["post-1355","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-business"],"_links":{"self":[{"href":"https:\/\/www.kredx.com\/blog\/wp-json\/wp\/v2\/posts\/1355","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.kredx.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kredx.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kredx.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kredx.com\/blog\/wp-json\/wp\/v2\/comments?post=1355"}],"version-history":[{"count":1,"href":"https:\/\/www.kredx.com\/blog\/wp-json\/wp\/v2\/posts\/1355\/revisions"}],"predecessor-version":[{"id":13993,"href":"https:\/\/www.kredx.com\/blog\/wp-json\/wp\/v2\/posts\/1355\/revisions\/13993"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.kredx.com\/blog\/wp-json\/wp\/v2\/media\/5891"}],"wp:attachment":[{"href":"https:\/\/www.kredx.com\/blog\/wp-json\/wp\/v2\/media?parent=1355"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kredx.com\/blog\/wp-json\/wp\/v2\/categories?post=1355"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kredx.com\/blog\/wp-json\/wp\/v2\/tags?post=1355"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}