Excel Freeze Top Row

The Header of a column is one of the most Important parts in a spreadsheet since it describes what values are stored in that column. Now if you have a large spreadsheet with many rows, one would obviously have to scroll down to work on different values. But once you scroll down, you don’t see the header anymore since by default it stays on the top. And once you can’t see the header the values that you see in different columns become meaningless since you don’t know what they are or what they represent.

Fortunately excel has some built in features that help view the header of the column even if they are on the last row of a 10,000 row spreadsheet. This is called freezing rows. In this case one would freeze the top row. In order to achieve this one would have to activate the View tab and click on “Freeze Top Row”.

 

Screen Shot 2018-01-18 at 9.34.38 PM.png

Advertisements

Excel remove Duplicates

With Excel 2016, Duplicates can be removed quite easily. Just select any row in the table . Then activate the data tab in the ribbon menu and click on the Remove Duplicates option in the Menu. And then select the columns where Duplicates should be removed. Once you do that, it would give you a notification as to the number of Duplicates removed and the number of remaining rows in the table.

Excel #### appearance 

Sometimes while working with numbers in excel you might come across the hash symbol ###. It might look as if the number which you entered in the cell has been replaced with hash symbols. For example 124.56 would look like ###.## . And once you hover the mouse over the the hash symbol, it would display the number as a tooltip. And you might be wondering what is going on here?

Well, sometimes when you come across this Issue it is primarily because there is not enough space for that number in the cell.

How can I resolve this?

In order to resolve this one would have to basically expand the column on which the cell is residing. That can be done by simply double clicking on the right bar of the column border.

Excel-Text Function

Most of us might have worked with Excel or some other spreadsheet software.  We might have used it for doing a variety of tasks. Now today, I am not going to brag about the features of Excel or any other Spreadsheet software for that matter. Probably, you all would be knowing what the pros and cons are!!

Recently I was working on a spreadsheet. It was about Mac Laptops and their specifications. And as you might be aware, the Operating Systems versions are in decimals. The Latest  OS X is “10.11” also known as ‘El Capitan’, named after a rock formation in Yosemite National Park, California. And after Apple releases a new version of an Operating System, they periodically release new updates.The version numbers would have two points. Apple recently released a new version of El Capitan, a couple of days ago, March 22nd to be precise. The Version Number was “10.11.4”  .

I was adding an entry for a laptop which had an OS X version of “10.10” also known as “Yosemite” which is again named from the Yosemite National Park in California. And as I was entering the number, I could only enter 10.1 and it wouldn’t allow me to enter 10.10 . I tried to find a solution by browsing the net. And I came up with a function called Text.

Text is a function which is used to convert a numeric value to a text. It can be also used to format numbers. Like adding more zeroes after a decimal place or adding symbols.

The formula goes something like this-

TEXT(Value, Format)

For Example-

=Text(23,”#.01″)

would add a one in the second place resulting in 23.01

And I found a way to enter 10.10 . I wrote

=Text(10,”#.10″)

and  it resulted in 10.10

I am listing some links where you could read more about the topic.

https://support.office.com/en-us/article/TEXT-function-20d5ac4d-7b94-49fd-bb38-93d29371225c

http://www.excelfunctions.net/Excel-Text-Function.html

http://www.techonthenet.com/excel/formulas/text.php

http://www.exceltrick.com/formulas_macros/excel-text-function/