MAKE IT SIMPLE

2 Easy Methods to Prevent Duplicate Values in a Column in Your Excel If you want to avoid entering duplicate values in a column in Excel, you can refer to this article. Here we will share you 2 simple approaches. At times, when you edit an Excel worksheet, you may want to ensure the data in a column are unique, namely preventing duplicate values in a column. It demands Excel to auto alert you every time when you enter duplicate values in a column. Thereinafter, we will share you 2 quick means to get it. Method 1：Prevent via “Data Validation” First off, select all the cells in a specific column, such as “Column A”. Then, turn to “Data” tab and click on “Data Validation” button in “Data Tools” group. Next, in the popup dialog box, on “Settings” tab, select “Custom” option from the drop down list of “Allow”. After that, input the following formula in the “Formula” field. =COUNTIF($A$1:$A$20,A1)=1 Later, switch to “Error Alert” tab in the current dialog box. Subse...

Simple Method to Select All Non-blank Cells in an Excel Worksheet First off, get access to Excel VBA editor using Alt + F11 Then, put the following code into an unused module. Sub SelectAllNonBlankCells() Dim objUsedRange As Range Dim objRange As Range Dim objNonblankRange As Range Set objUsedRange = Application.ActiveSheet.UsedRange For Each objRange In objUsedRange If Not (objRange.Value = "") Then If objNonblankRange Is Nothing Then Set objNonblankRange = objRange Else Set objNonblankRange = Application.Union(objNonblankRange, objRange) End If End If Next If Not (objNonblankRange Is Nothing) Then objNonblankRange.Select End If End Sub After that, exit the VBA editor and add this macro to Quick Access Toolbar. Now, open your desired worksheet and click the macro button. At once, all non-blank cells will be selected, as shown in the f...

Please open Virtual Basic using Developer toolbar or pressing shortcut Alt + F11 & Insert new Module from Insert Menu or by right click in Project Explorer. Copy the underneath program in the module. Now move to the excel workbook and try the Formula "=SpellNumber(<number>)" You will get the number converted in Words. For any query please do comment or mail me on Gauravj75@gmail.com Public Function SpellNumbers(ByVal MyNumber) Dim Rupees, Paise, Temp Dim DecimalPlace, Count ReDim Place(9) As String Place(2) = " Thousand " Place(3) = " Lac " Place(4) = " Crore " Place(5) = " Arab " ' String representation of amount MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none DecimalPlace = InStr(MyNumber, ".") 'Convert Paise and set MyNumber to Rupee amount If DecimalPlace > 0 Then Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)) MyNumber = Tr...

Particluars Shortcuts Create new Excel File Ctrl N Create New Sheet Shift F11 Move Between Sheets Clockwise Ctrl PageDown Move Between Sheets Anti-Clockwise Ctrl PageUp Move between opened Excel Workbooks Ctrl Tab Undo Ctrl Z Redo Ctrl Y Save Ctrl S Save As F12 Insert Table Ctrl L Zoom In Ctrl Alt + Zoom Out Ctrl Alt - Hide/Unhide the Toolbar Ctrl F1 Right-Clicking Menu Shift F10 Display Control Menu Alt SpaceBar Close Excel File Ctrl F4 Switch the Fn mode Fn Esc Go to Cell A1 Ctrl Home Go to Last Cell of Data Ctrl End Select Whole...

Introduction Data validation is a feature in Excel used to control what a user can enter into a cell. For example, you could use data validation to make sure a value is a number between 1 and 6, make sure a date occurs in the next 30 days, or make sure a text entry is less than 25 characters. Data validation can simply display a message to a user telling them what is allowed as shown below: Data validation can also stop invalid user input. For example, if a product code fails validation, you can display a message like this: In addition, data validation can be used to present the user with a predefined choice in a dropdown menu: This can be a convenient way to give a user exactly the values that meet requirements. Data validation controls Data validation is implemented via rules defined in Excel's user interface on the Data tab of the ribbon. Important limitation It is important to understand that data validation can be easily defeated. If a user copies d...