Shortcut Key for Converting Value In Cell to Absolute Value

Copper Contributor

In Office Home & Student 2021, I need an absolute value shortcut key(s) to change many possibly negative numbers in a spreadsheet to absolute values.  A general search has yielded shortcuts, but they haven't worked.   Any ideas please.

4 Replies

@AlexProfile 

There is no built-in shortcut for this. You could create a macro in your personal macro workbook PERSONAL.XLSB and assign a custom keyboard shortcut to the macro.

Sub Convert2Absolute()
    Dim rng As Range
    Dim cel As Range
    Application.ScreenUpdating = False
    On Error Resume Next
    Set rng = Selection.SpecialCells(xlCellTypeConstants, xlNumbers)
    If Not rng Is Nothing Then
        For Each cel In rng
            cel.Value = Abs(cel.Value)
        Next cel
    End If
    Application.ScreenUpdating = True
End Sub

Select a range before running the macro. Cells with formulas and cells that don't contain a number will be ignored by the macro.

@Hans Vogelaar 

Hi,

Thanks for the help.  I'm surprised they eliminated shortcuts for this, which apparently existed in earlier versions of Excel.

thanks again,

Alex Phillips

 

@AlexProfile 

As far as I know, there has never been a built-in shortcut for this. I've been using Excel since 1986.

Thanks for telling me so I give up the eternal search for one.

Alex
I will try your macro either today or tomorrow.